userid name checktime checktype qdflag ctime
1 张三 2011-06-11 08:10:01 I 1 08:10:01
1 张三 2011-06-11 08:12:01 I 1 08:12:01
1 张三 2011-06-11 08:34:01 I 1 08:14:01
1 张三 2011-06-11 18:10:01 I 1 08:10:01
1 张三 2011-06-11 18:12:01 O 3 08:12:01
1 张三 2011-06-11 18:14:01 O 3 08:14:01
2 李四 2011-06-11 08:10:01 O 3 08:10:01
2 李四 2011-06-11 08:12:01 I 1 08:12:01
2 李四 2011-06-11 08:14:01 I 1 08:14:01
2 李四 2011-06-11 17:58:01 O 3 08:10:01
2 李四 2011-06-11 18:12:01 O 3 08:12:01
2 李四 2011-06-11 18:14:01 O 3 08:14:01
以上是oracle 表中考勤数据其中:userid 考勤号码,name员工姓名,checktime签到时间,checktype签到类型(I是签到,O是签退),qdflag签到标志(1是早上签到,3是下午签到)现在能否选择出如下效果userid name checktime checktype qdflag ctime
1 张三 2011-06-11 08:10:01 I 1 08:10:011 张三 2011-06-11 18:10:01 I 1 18:10:012 李四 2011-06-11 08:10:01 O 3 08:10:012 李四 2011-06-11 17:58:01 O 3 17:58:01
1 张三 2011-06-11 08:10:01 I 1 08:10:01
1 张三 2011-06-11 08:12:01 I 1 08:12:01
1 张三 2011-06-11 08:34:01 I 1 08:14:01
1 张三 2011-06-11 18:10:01 I 1 08:10:01
1 张三 2011-06-11 18:12:01 O 3 08:12:01
1 张三 2011-06-11 18:14:01 O 3 08:14:01
2 李四 2011-06-11 08:10:01 O 3 08:10:01
2 李四 2011-06-11 08:12:01 I 1 08:12:01
2 李四 2011-06-11 08:14:01 I 1 08:14:01
2 李四 2011-06-11 17:58:01 O 3 08:10:01
2 李四 2011-06-11 18:12:01 O 3 08:12:01
2 李四 2011-06-11 18:14:01 O 3 08:14:01
以上是oracle 表中考勤数据其中:userid 考勤号码,name员工姓名,checktime签到时间,checktype签到类型(I是签到,O是签退),qdflag签到标志(1是早上签到,3是下午签到)现在能否选择出如下效果userid name checktime checktype qdflag ctime
1 张三 2011-06-11 08:10:01 I 1 08:10:011 张三 2011-06-11 18:10:01 I 1 18:10:012 李四 2011-06-11 08:10:01 O 3 08:10:012 李四 2011-06-11 17:58:01 O 3 17:58:01
select * from (
select t.*,
row_number over (partition by userid,uname order by checktype asc) rn1
from t
where rn1=1
union all
select t.*,
row_number over (partition by userid,uname order by checktype desc) rn2
from t
where rn2=1)
order by userid,name
with t as(
select 1 userid,'张三' name,to_date('2011-06-11 08:10:01','yyyy-mm-dd hh24:mi:ss') checktime,'I' checktype,1 qdflag,'08:10:01' ctime from dual union all
select 1,'张三',to_date('2011-06-11 08:12:01','yyyy-mm-dd hh24:mi:ss'),'I',1,'08:12:01' from dual union all
select 1,'张三',to_date('2011-06-11 08:34:01','yyyy-mm-dd hh24:mi:ss'),'I',1,'08:14:01' from dual union all
select 1,'张三',to_date('2011-06-11 18:10:01','yyyy-mm-dd hh24:mi:ss'),'I',1,'08:10:01' from dual union all
select 1,'张三',to_date('2011-06-11 18:12:01','yyyy-mm-dd hh24:mi:ss'),'O',3,'08:12:01' from dual union all
select 1,'张三',to_date('2011-06-11 18:14:01','yyyy-mm-dd hh24:mi:ss'),'O',3,'08:14:01' from dual union all
select 2,'李四',to_date('2011-06-11 08:10:01','yyyy-mm-dd hh24:mi:ss'),'O',3,'08:10:01' from dual union all
select 2,'李四',to_date('2011-06-11 08:12:01','yyyy-mm-dd hh24:mi:ss'),'I',1,'08:12:01' from dual union all
select 2,'李四',to_date('2011-06-11 08:14:01','yyyy-mm-dd hh24:mi:ss'),'I',1,'08:14:01' from dual union all
select 2,'李四',to_date('2011-06-11 17:58:01','yyyy-mm-dd hh24:mi:ss'),'O',3,'08:10:01' from dual union all
select 2,'李四',to_date('2011-06-11 18:12:01','yyyy-mm-dd hh24:mi:ss'),'O',3,'08:12:01' from dual union all
select 2,'李四',to_date('2011-06-11 18:14:01','yyyy-mm-dd hh24:mi:ss'),'O',3,'08:14:01' from dual)
select userid,name,checktime,checktype,qdflag,ctime
from (
select t.*,
row_number() over (partition by userid,name order by checktime asc) rn1,
row_number() over (partition by userid,name order by checktime desc) rn2
from t)
where rn1=1 or rn2=1
order by userid,name
/ USERID NAME CHECKTIME C QDFLAG CTIME
---------- ---- ------------------- - ---------- --------
1 张三 2011-06-11 08:10:01 I 1 08:10:01
1 张三 2011-06-11 18:14:01 O 3 08:14:01
2 李四 2011-06-11 08:10:01 O 3 08:10:01
2 李四 2011-06-11 18:14:01 O 3 08:14:01
1 张三 2011-06-11 08:10:01 I 1 08:10:01
1 张三 2011-06-11 08:12:01 I 1 08:12:01
1 张三 2011-06-11 13:34:01 I 3 13:34:01
1 张三 2011-06-11 14:10:01 I 3 14:10:01
2 李四 2011-06-11 08:10:01 I 1 08:10:01
2 李四 2011-06-11 08:12:01 I 1 08:12:01
2 李四 2011-06-11 13:59:01 I 3 13:59:01
2 李四 2011-06-11 14:05:01 I 3 14:05:01
不好意思,刚才表述错了。能否达到如下效果
userid name checktime checktype qdflag ctime
1 张三 2011-06-11 08:10:01 I 1 08:10:01
1 张三 2011-06-11 13:34:01 I 3 13:34:01
2 李四 2011-06-11 08:10:01 I 1 08:10:01
2 李四 2011-06-11 13:59:01 I 3 13:59:01
select userid,name,checktime,checktype,qdflag,ctime
from (
select t.*,
row_number() over (partition by userid,name,qdflag order by checktime asc) rn
from t)
where rn=1
SQL> with t as(
2 select 1 userid,'张三' name,to_date('2011-06-11 08:10:01','yyyy-mm-dd hh24:mi:ss') checktime,'I' checktype,1 qdflag,'08:10:01' ctime from dual union all
3 select 1,'张三',to_date('2011-06-11 08:12:01','yyyy-mm-dd hh24:mi:ss'),'I',1,'08:12:01' from dual union all
4 select 1,'张三',to_date('2011-06-11 13:34:01','yyyy-mm-dd hh24:mi:ss'),'I',3,'13:34:01' from dual union all
5 select 1,'张三',to_date('2011-06-11 14:10:01','yyyy-mm-dd hh24:mi:ss'),'I',3,'14:10:01' from dual union all
6 select 2,'李四',to_date('2011-06-11 08:10:01','yyyy-mm-dd hh24:mi:ss'),'I',1,'08:10:01' from dual union all
7 select 2,'李四',to_date('2011-06-11 08:12:01','yyyy-mm-dd hh24:mi:ss'),'I',1,'08:12:01' from dual union all
8 select 2,'李四',to_date('2011-06-11 13:59:01','yyyy-mm-dd hh24:mi:ss'),'I',3,'13:59:01' from dual union all
9 select 2,'李四',to_date('2011-06-11 14:05:01','yyyy-mm-dd hh24:mi:ss'),'I',3,'14:05:01' from dual)
10 select userid,name,checktime,checktype,qdflag,ctime
11 from (
12 select t.*,
13 row_number() over (partition by userid,name,qdflag order by checktime asc) rn
14 from t)
15 where rn=1
16 /
USERID NAME CHECKTIME C QDFLAG CTIME
---------- ---- ------------------- - ---------- --------
1 张三 2011-06-11 08:10:01 I 1 08:10:01
1 张三 2011-06-11 13:34:01 I 3 13:34:01
2 李四 2011-06-11 08:10:01 I 1 08:10:01
2 李四 2011-06-11 13:59:01 I 3 13:59:01
USERID NAME CHECKTIME CHECKTYPE QDFLAG CTIME
---------- ---- ----------- --------- ---------- --------
1 张三 2011-6-11 8 I 1 08:10:01
1 张三 2011-6-11 8 I 1 08:12:01
1 张三 2011-6-11 1 I 3 13:34:01
1 张三 2011-6-11 1 I 3 14:10:01
2 李四 2011-6-11 8 I 1 08:10:01
2 李四 2011-6-11 8 I 1 08:12:01
2 李四 2011-6-11 1 I 3 13:59:01
2 李四 2011-6-11 1 I 3 14:05:01
8 rows selected
SQL>
SQL> SELECT * FROM tt a WHERE NOT EXISTS
2 (SELECT * FROM tt b WHERE b.userid = a.userid
3 AND b.checktype = a.checktype AND b.qdflag = a.qdflag
4 AND b.ctime < a.ctime)
5 ORDER BY userid, qdflag;
USERID NAME CHECKTIME CHECKTYPE QDFLAG CTIME
---------- ---- ----------- --------- ---------- --------
1 张三 2011-6-11 8 I 1 08:10:01
1 张三 2011-6-11 1 I 3 13:34:01
2 李四 2011-6-11 8 I 1 08:10:01
2 李四 2011-6-11 1 I 3 13:59:01
SQL>