USERID CHECKTIME CTIME SBZT
2810 2011-6-21 8:14:43 8:14:43 1
2810 2011-6-23 7:56:55 7:56:55 1
2812 2011-6-21 8:23:52 8:23:52 1
2812 2011-6-21 8:14:15 8:14:15 1
2812 2011-6-21 8:14:13 8:14:13 1
2812 2011-6-21 8:14:00 8:14:00 1
3149 2011-6-23 8:30:28 8:30:28 1
3149 2011-6-23 8:24:25 8:24:25 1
3149 2011-6-23 8:16:26 8:16:26 1
3149 2011-6-23 8:12:57 8:12:57 1
3150 2011-6-23 8:27:35 8:27:35 1
3150 2011-6-22 8:27:58 8:27:58 1
3150 2011-6-23 7:42:51 7:42:51 1
3820 2011-6-23 14:36:43 14:36:43 6
3820 2011-6-23 14:36:30 14:36:30 6
2883 2011-6-20 13:54:32 13:54:32 6
2886 2011-6-20 13:37:07 13:37:07 6
2886 2011-6-20 13:42:22 13:42:22 6
2886 2011-6-20 13:41:48 13:41:48 6
2886 2011-6-20 13:41:46 13:41:46 6
2886 2011-6-20 13:40:55 13:40:55 6
2886 2011-6-20 13:40:40 13:40:40 6
2675 2011-6-16 14:43:14 14:43:14 6
上述为考勤记录,其中包含重复打卡记录。USERID为员工号,CHECKTIME为打卡时间,CTIME时间,SBZT(1:为上班,2为迟到6为下午上班。),现如何能将重复的考勤筛选掉。
2810 2011-6-21 8:14:43 8:14:43 1
2810 2011-6-23 7:56:55 7:56:55 1
2812 2011-6-21 8:23:52 8:23:52 1
2812 2011-6-21 8:14:15 8:14:15 1
2812 2011-6-21 8:14:13 8:14:13 1
2812 2011-6-21 8:14:00 8:14:00 1
3149 2011-6-23 8:30:28 8:30:28 1
3149 2011-6-23 8:24:25 8:24:25 1
3149 2011-6-23 8:16:26 8:16:26 1
3149 2011-6-23 8:12:57 8:12:57 1
3150 2011-6-23 8:27:35 8:27:35 1
3150 2011-6-22 8:27:58 8:27:58 1
3150 2011-6-23 7:42:51 7:42:51 1
3820 2011-6-23 14:36:43 14:36:43 6
3820 2011-6-23 14:36:30 14:36:30 6
2883 2011-6-20 13:54:32 13:54:32 6
2886 2011-6-20 13:37:07 13:37:07 6
2886 2011-6-20 13:42:22 13:42:22 6
2886 2011-6-20 13:41:48 13:41:48 6
2886 2011-6-20 13:41:46 13:41:46 6
2886 2011-6-20 13:40:55 13:40:55 6
2886 2011-6-20 13:40:40 13:40:40 6
2675 2011-6-16 14:43:14 14:43:14 6
上述为考勤记录,其中包含重复打卡记录。USERID为员工号,CHECKTIME为打卡时间,CTIME时间,SBZT(1:为上班,2为迟到6为下午上班。),现如何能将重复的考勤筛选掉。
--去掉重复的?是说去掉上班或者下班时间之内重复的数据吗?
select userid,checktime,ctime,sbzt from (
select t.*,row_number() over (partition by userid,sbzt order by checktime) rn
from tb_name t)
where rn=1
--你应该给出部分结果集.................
--仔细看看是你想要的数据吗?
SQL> with t as(
2 select '2810' userid,to_date('2011-6-21 8:14:43','yyyy-mm-dd hh24:mi:ss') checktime,'8:14:43' ctime,1 sbzt from dual union all
3 select '2810',to_date('2011-6-23 7:56:55','yyyy-mm-dd hh24:mi:ss'),'7:56:55',1 from dual union all
4 select '2812',to_date('2011-6-21 8:23:52','yyyy-mm-dd hh24:mi:ss'),'8:23:52',1 from dual union all
5 select '2812',to_date('2011-6-21 8:14:15','yyyy-mm-dd hh24:mi:ss'),'8:14:15',1 from dual union all
6 select '2812',to_date('2011-6-21 8:14:13','yyyy-mm-dd hh24:mi:ss'),'8:14:13',1 from dual union all
7 select '2812',to_date('2011-6-21 8:14:00','yyyy-mm-dd hh24:mi:ss'),'8:14:00',1 from dual union all
8 select '3149',to_date('2011-6-23 8:30:28','yyyy-mm-dd hh24:mi:ss'),'8:30:28',1 from dual union all
9 select '3149',to_date('2011-6-23 8:24:25','yyyy-mm-dd hh24:mi:ss'),'8:24:25',1 from dual union all
10 select '3149',to_date('2011-6-23 8:16:26','yyyy-mm-dd hh24:mi:ss'),'8:16:26',1 from dual union all
11 select '3149',to_date('2011-6-23 8:12:57','yyyy-mm-dd hh24:mi:ss'),'8:12:57',1 from dual union all
12 select '3150',to_date('2011-6-23 8:27:35','yyyy-mm-dd hh24:mi:ss'),'8:27:35',1 from dual union all
13 select '3150',to_date('2011-6-22 8:27:58','yyyy-mm-dd hh24:mi:ss'),'8:27:58',1 from dual union all
14 select '3150',to_date('2011-6-23 7:42:51','yyyy-mm-dd hh24:mi:ss'),'7:42:51',1 from dual union all
15 select '3820',to_date('2011-6-23 14:36:43','yyyy-mm-dd hh24:mi:ss'),'14:36:43',6 from dual union all
16 select '3820',to_date('2011-6-23 14:36:30','yyyy-mm-dd hh24:mi:ss'),'14:36:30',6 from dual union all
17 select '2883',to_date('2011-6-20 13:54:32','yyyy-mm-dd hh24:mi:ss'),'13:54:32',6 from dual union all
18 select '2886',to_date('2011-6-20 13:37:07','yyyy-mm-dd hh24:mi:ss'),'13:37:07',6 from dual union all
19 select '2886',to_date('2011-6-20 13:42:22','yyyy-mm-dd hh24:mi:ss'),'13:42:22',6 from dual union all
20 select '2886',to_date('2011-6-20 13:41:48','yyyy-mm-dd hh24:mi:ss'),'13:41:48',6 from dual union all
21 select '2886',to_date('2011-6-20 13:41:46','yyyy-mm-dd hh24:mi:ss'),'13:41:46',6 from dual union all
22 select '2886',to_date('2011-6-20 13:40:55','yyyy-mm-dd hh24:mi:ss'),'13:40:55',6 from dual union all
23 select '2886',to_date('2011-6-20 13:40:40','yyyy-mm-dd hh24:mi:ss'),'13:40:40',6 from dual union all
24 select '2675',to_date('2011-6-16 14:43:14','yyyy-mm-dd hh24:mi:ss'),'14:43:14',6 from dual)
25 select userid,checktime,ctime,sbzt from (
26 select t.*,row_number() over (partition by userid,trunc(checktime),sbzt order by checktime) rn
27 from t)
28 where rn=1
29 order by userid
30 /
USERID CHECKTIME CTIME SBZT
------ ----------- -------- ----------
2675 2011-06-16 14:43:14 6
2810 2011-06-21 8:14:43 1
2810 2011-06-23 7:56:55 1
2812 2011-06-21 8:14:00 1
2883 2011-06-20 13:54:32 6
2886 2011-06-20 13:37:07 6
3149 2011-06-23 8:12:57 1
3150 2011-06-22 8:27:58 1
3150 2011-06-23 7:42:51 1
3820 2011-06-23 14:36:30 6
10 rows selected
row_number() over (partition by userid,trunc(checktime),sbzt order by checktime) rn
-----------
学习学习。