表结构如下：
f_ReadDate f_ConsumerName
2012-9-29 8:31:03 lanyang
2012-9-29 8:31:04 lanyang
2012-9-29 8:28:23 wangxiao
2012-9-29 8:27:12 chenchunli
2012-9-29 8:27:01 chenchunli
2012-9-29 8:26:33 liuzg
2012-9-29 8:25:22 zhouar
2012-9-29 8:25:21 tangyc
2012-9-29 8:31:12 zhangsh
2012-9-29 8:21:33 yangmei
2012-9-29 8:21:45 yangmei
2012-9-29 8:21:23 jianghy
2012-9-29 8:21:15 jianghy
2012-9-29 8:09:12 liuyi
2012-9-29 8:09:22 zhangsf
2012-9-28 17:44:15 tangyc
2012-9-28 17:43:15 tangyc
2012-9-28 17:41:23 liuyi
2012-9-28 17:40:14 jianghy
这是个上下班打卡的记录，现在想筛选数据，一是上班有两条记录以上的，选择时间靠后的一条记录，下班有两条以上记录的，选择时间靠前的。
------解决方案--------------------
看下是否可以
- SQL code
--> 测试数据：[tb]IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]GO CREATE TABLE [tb]([f_ReadDate] DATETIME,[f_ConsumerName] VARCHAR(10))INSERT [tb]SELECT '2012-9-29 8:31:03','lanyang' UNION ALLSELECT '2012-9-29 8:31:04','lanyang' UNION ALLSELECT '2012-9-29 8:28:23','wangxiao' UNION ALLSELECT '2012-9-29 8:27:12','chenchunli' UNION ALLSELECT '2012-9-29 8:27:01','chenchunli' UNION ALLSELECT '2012-9-29 8:26:33','liuzg' UNION ALLSELECT '2012-9-29 8:25:22','zhouar' UNION ALLSELECT '2012-9-29 8:25:21','tangyc' UNION ALLSELECT '2012-9-29 8:31:12','zhangsh' UNION ALLSELECT '2012-9-29 8:21:33','yangmei' UNION ALLSELECT '2012-9-29 8:21:45','yangmei' UNION ALLSELECT '2012-9-29 8:21:23','jianghy' UNION ALLSELECT '2012-9-29 8:21:15','jianghy' UNION ALLSELECT '2012-9-29 8:09:12','liuyi' UNION ALLSELECT '2012-9-29 8:09:22','zhangsf' UNION ALLSELECT '2012-9-28 17:44:15','tangyc' UNION ALLSELECT '2012-9-28 17:43:15','tangyc' UNION ALLSELECT '2012-9-28 17:41:23','liuyi' UNION ALLSELECT '2012-9-28 17:40:14','jianghy'--------------开始查询--------------------------SELECT [f_ReadDate] , [f_ConsumerName] , 状态FROM ( SELECT * , 状态 = CASE WHEN CONVERT(VARCHAR(8) , [f_ReadDate] , 108) > '08:30:59' THEN '迟到' ELSE '正常上班' END , 1 AS 上下班 FROM [tb] AS t WHERE NOT EXISTS ( SELECT 1 FROM tb WHERE CONVERT(VARCHAR(8) , [f_ConsumerName] , 112) = CONVERT(VARCHAR(8) , t.[f_ConsumerName] , 112) AND [f_ReadDate] > t.[f_ReadDate] AND CONVERT(VARCHAR(8) , [f_ReadDate] , 108) < '12:00:00' ) AND CONVERT(VARCHAR(8) , [f_ReadDate] , 108) < '12:00:00' UNION ALL SELECT * , 状态 = CASE WHEN CONVERT(VARCHAR(8) , [f_ReadDate] , 108) < '12:00:00' THEN '早退' ELSE '正常下班' END , 2 AS 上下班 FROM [tb] AS t WHERE NOT EXISTS ( SELECT 1 FROM tb WHERE CONVERT(VARCHAR(8) , [f_ConsumerName] , 112) = CONVERT(VARCHAR(8) , t.[f_ConsumerName] , 112) AND [f_ReadDate] > t.[f_ReadDate] AND CONVERT(VARCHAR(8) , [f_ReadDate] , 108) > '13:00:00' ) AND CONVERT(VARCHAR(8) , [f_ReadDate] , 108) > '13:00:00' ) AS tORDER BY [f_ConsumerName] , 上下班 ----------------结果----------------------------/* f_ReadDate f_ConsumerName 状态2012-09-29 08:27:12.000 chenchunli 正常上班2012-09-29 08:21:23.000 jianghy 正常上班2012-09-28 17:40:14.000 jianghy 正常下班2012-09-29 08:31:04.000 lanyang 迟到2012-09-29 08:09:12.000 liuyi 正常上班2012-09-28 17:41:23.000 liuyi 正常下班2012-09-29 08:26:33.000 liuzg 正常上班2012-09-29 08:25:21.000 tangyc 正常上班2012-09-28 17:44:15.000 tangyc 正常下班2012-09-29 08:28:23.000 wangxiao 正常上班2012-09-29 08:21:45.000 yangmei 正常上班2012-09-29 08:09:22.000 zhangsf 正常上班2012-09-29 08:31:12.000 zhangsh 迟到2012-09-29 08:25:22.000 zhouar 正常上班*/