在线等。过滤重复打卡记录：
--员工考勤记录表,一天正常情况为打四次卡
create table test(
Emp_Card varchar(20), --员工编号
ClockDate datetime, --打卡日期
ClockTime datetime, --打卡时间
)
insert into test select '001','2012-09-01','07:55:54' union all--早上上班001重复打了三次
select '001','2012-09-01','07:54:56' union all
select '001','2012-09-01','07:54:58' union all
select '001','2012-09-01','12:04:36' union all--中午下班
select '001','2012-09-01','13:27:19' union all--下午上班
select '001','2012-09-01','18:03:19' union all--下午下班
-------------------------
select '002','2012-09-01','07:32:12' union all--002的打卡记录
select '002','2012-09-01','12:03:32' union all--中午下班
select '002','2012-09-01','13:23:15' union all--下午上班
select '002','2012-09-01','18:11:13' union all--下午下班
-------------------------
select '003','2012-09-01','07:33:14' --003的打卡记录，只打了一条。
---我现在想要的是筛选出一员工上班时间的最小打卡时间，及最大下班时间，即过虑掉重复打卡记录即可，结果如下:
'001','2012-09-01','07:55:54'--上班
'001','2012-09-01','12:04:36'--下班
'001','2012-09-01','13:27:19'--上班
'001','2012-09-01','18:03:19'--下班
------------------------------
'002','2012-09-01','07:32:12' --002的打卡记录
'002','2012-09-01','12:03:32' --中午下班
'002','2012-09-01','13:23:15' --下午上班
'002','2012-09-01','18:11:13' --下午下班
-----------------------------
'003','2012-09-01','07:33:14'
--请问怎么实现？
[最优解释]
SELECT *
FROM test t
WHERE (ClockDate = '2012-09-01') AND (ClockTime BETWEEN '2012-09-01 06:00:00' AND
'2012-09-01 10:00:00') and not exists(
select 1 from test a where
a.Emp_Card= t.Emp_Card and a.ClockTime < t.ClockTime
and (ClockDate = '2012-09-01') AND (ClockTime BETWEEN '2012-09-01 06:00:00' AND
'2012-09-01 10:00:00')
)
--http://topic.csdn.net/u/20100311/15/371af678-3056-4c73-9282-9ed4eac8394e.html
