最近，遇到并解决一个SQL上的疑难问题。考勤系统，记录着员工进出公司的刷卡记录。而员工刷卡并不规范，存在刷多次的情况。例如：出去时连续刷多次，进来时也连续刷多次。筛选有效刷卡记录数据的规则：对于出去时连续刷多次（包含一次）的情况，取第一次刷卡记录；对于进来时连续刷多次（包含一次）的情况，取最后一次的刷卡记录。考勤系统的数据量很大，假设公司有2万名员工，一员工一天100条刷卡记录。
用什么方法可以高效地查出某一时间范围内员工的有效刷卡记录？
测试表及测试数据如下：
create table Attendance ( UserId int, --员工ID ClockInTime datetime, --员工刷卡时间 Flag char(1) --进出标志 '1'代表出,'0'代表进 ) insert Attendance values(100001,'2015-06-01 08:03:00','1'), (100001,'2015-06-01 08:03:10','1'), (100001,'2015-06-01 08:03:50','1'), (100001,'2015-06-01 08:04:00','1'), (100001,'2015-06-01 08:10:00','0'), (100001,'2015-06-01 08:10:10','0'), (100001,'2015-06-01 08:15:00','1'), (100001,'2015-06-01 08:30:00','1'), (100001,'2015-06-01 08:40:10','0'), (100001,'2015-06-01 09:00:00','1'), (100001,'2015-06-01 09:15:10','0'), (100001,'2015-06-01 09:30:00','1'), (100002,'2015-06-01 08:03:00','0'), (100002,'2015-06-01 08:03:10','0'), (100002,'2015-06-01 08:03:50','1'), (100002,'2015-06-01 08:04:00','1'), (100002,'2015-06-01 08:10:00','1'), (100002,'2015-06-01 08:10:10','0'), (100002,'2015-06-01 08:15:00','1'), (100002,'2015-06-01 08:30:00','1'), (100002,'2015-06-01 08:40:10','1'), (100002,'2015-06-01 09:00:00','0'), (100002,'2015-06-01 09:15:10','0'), (100002,'2015-06-01 09:30:00','1')
而需筛选的有效数据为红色标记部分：
而作为测试数据，也就只提供两名员工，每人一天12条的刷卡记录，需要完成的是将红色标记的数据筛选出来。
不难看出问题的难点在于判断哪些数据是连续（进或出）的，无论出去还是进来。把这一点解决了，所有的问题也就迎刃而解。
1）首先，想到了递归查询。但是很快否定了想法，这个方法判断不出来数据是否连续。
2）其次，又考虑游标。或许游标能判断是否连续的问题，但是处理大数据量时，性能肯定极其低。
最后，闪现一个思路，没想到顺着这个思路把问题解决了。
1，先按UserID、日期分组，组内按ClockInTime升序排列。
1 2 3 | select *,ROW_NUMBER() over(partition by UserId,convert(varchar(10),ClockInTime,23) order by ClockInTime) as RN into #tmp from Attendance <br><br>select * from #tmp order by UserId,ClockInTime |
结果如图：
2，再按UserID、日期、Flag分组，组内按ClockInTime升序排列。
1 2 3 | select *,ROW_NUMBER() over(partition by UserId,convert(varchar(10),ClockInTime),Flag order by ClockInTime) as RN1 into #tmp1from Attendance <br><br>select * from #tmp1 order by UserId,ClockInTime |
结果如图：
3，用#tmp1中的RN1与#tmp中的RN做差。
select a.*,b.RN1,b.RN1-a.RN as RN2 into #tmp2 from #tmp as a,#tmp1 as b where a.UserId=b.UserId and a.ClockInTime=b.ClockInTime and a.Flag=b.Flag
select * from #tmp2 order by UserId,ClockInTime
结果如图：
3，根据UserID、日期、Flag、RN2可以判断出哪些数据是连续的，然后，用Row_Number()排序一下，就可以筛选所需要的数据。
1 2 3 4 5 | select *, case when Flag='1' then ROW_NUMBER() over(Partition by UserID,convert(varchar(10),ClockInTime,23),Flag,RN2 order by ClockInTime) else ROW_NUMBER() over(Partition by UserID,convert(varchar(10),ClockInTime,23),Flag,RN2 order by ClockInTime desc) end as RId into #tmp3 from #tmp2 <br><br>select * from #tmp3 order by UserId,ClockInTime |
结果如图：
4，RID=‘1’的数据是正确的结果，即有效的刷卡记录数据。
1 2 3 4 | select UserId,ClockInTime,Flag from #tmp3 where Rid='1'order by UserId,ClockInTime |
结果如图：
这样问题就解决了。进一步优化sql，其实1，2，3等3个步骤只要一步就解决了
1 2 3 | select *,ROW_NUMBER() over(partition by UserId,convert(varchar(10),ClockInTime,23) order by ClockInTime)-ROW_NUMBER() over(partition by UserId,convert(varchar(10),ClockInTime),Flag order by ClockInTime) as RNfrom Attendance order by UserId,ClockInTime |
有了上面查询的结果，后面的查询也简单多了。如果用一句SQL来解决的话，那就是：
1 2 3 4 5 6 7 8 9 10 11 | select UserId,ClockInTime,Flag from (select *,case when Flag='1' then ROW_NUMBER() over(Partition by UserID,convert(varchar(10),ClockInTime,23),Flag,RN order by ClockInTime) else ROW_NUMBER() over(Partition by UserID,convert(varchar(10),ClockInTime,23),Flag,RN order by ClockInTime desc) end as RIdfrom (select *,ROW_NUMBER() over(partition by UserId,convert(varchar(10),ClockInTime,23) order by ClockInTime)-ROW_NUMBER() over(partition by UserId,convert(varchar(10),ClockInTime),Flag order by ClockInTime) as RNfrom Attendance) as a) as b where RId='1' order by UserId,ClockInTime |
