消费数据进行相关查询和删除重复数据操作

select clock_id,emp_id,card_id,sign_time,card_consume,ID
from mealrecords
where sign_time >='2016-02-01'


select clock_id,emp_id,card_id,sign_time,card_consume
from mealrecords
where sign_time in (select sign_time from mealrecords group by sign_time having count(1) >= 2) and
card_id in (select sign_time from mealrecords group by sign_time having count(1) >= 2)



******鏌ヨ?鏄嗗北閲嶅?娑堣垂鏁版嵁******
select count(*),E.emp_id,E.card_id,E.emp_fname,E.sign_time
from (
SELECT A.emp_id as emp_id,A.card_id as card_id,A.emp_fname as emp_fname,B.sign_time as sign_time,C.depart_name as depart_name
FROM Employee A
left join mealrecords B on A.emp_id = B.emp_id
left join Departs C on C.depart_id=A.depart_id
where B.sign_time >= '2016-02-01' and A.link_man='鏄嗗北椤哄▉') as E
group by E.emp_id,E.emp_fname,E.depart_name,E.card_id,E.sign_time having count(sign_time)>1


*****鏌ヨ?鏄嗗北娑堣垂鏁版嵁鎵ц?缁撴灉*****
select clock_id,emp_id,card_id,sign_time,card_consume
from mealrecords
where card_id='602481' and sign_time >= '2016-03-01 '

*****鍒犻櫎鏄嗗北閲嶅?娑堣垂鏁版嵁******
delete from mealrecords where sign_time in(
select E.sign_time
from Mealrecords as E
left join Employee A on A.emp_id = E.emp_id
where E.sign_time >= '2016-02-01' and A.link_man='鏄嗗北椤哄▉'
group by E.emp_id,E.card_id,E.sign_time having count(E.sign_time)>1
)
and id not in
(
select min(E.id)
from Mealrecords as E
left join Employee A on A.emp_id = E.emp_id
where E.sign_time >= '2016-02-01' and A.link_man='鏄嗗北椤哄▉'
group by E.emp_id,E.sign_time having count(E.sign_time)>1
)


*******************************
select min(E.id)
from Mealrecords as E
left join Employee A on A.emp_id = E.emp_id
where E.sign_time >= '2016-02-01' and A.link_man='鏄嗗北椤哄▉'
group by E.emp_id,E.sign_time having count(E.sign_time)>1