with t as (select '张三' names,'10:30' btime,'11:30' etime,'a' type,'2016/11/1' rq from dual union all select '张三' names,'12:30' btime,'13:30' etime,'a' type,'2016/11/1' rq from dual union all select '张三' names,'10:32' btime,'11:30' etime,'a' type,'2016/11/3' rq from dual union all select '张三' names,'18:30' btime,'19:30' etime,'a' type,'2016/11/4' rq from dual) select a.names,a.btime,a.etime,a.type,b.btime,b.etime,b.type from (select m.*,row_number()over(partition by names order by rq,btime) rn from t m where rq='2016/11/1') a full outer join (select m.*,row_number()over(partition by names order by rq,btime) rn from t m where rq='2016/11/3') b on a.names = b.names and a.rn = b.rn ; 很麻烦,给你一个思路,帮你写了2天的
很麻烦,给你一个思路,帮你写了2天的。 你必须每一天都创建一个子查询 即(select m.*,row_number()over(partition by names order by rq,btime) rn from t m where rq='2016/11/1') 这样,然后关联第二个子查询 rq='2016/11/2'的,用全外连接关联,a.names = b.names and a.rn = b.rn匹配。另外只能2天用一个全外连接,如果你再接着连接第3天的,会出现一些问题的。这样你就需要大量的子查询和嵌套。
以上是类型考勤相关的表,不可用姓名关联,可能存在重名的情况,须有一张员工表,用员工ID关联,下表中将员工ID代入,仅供参考,只写了五天的数据 --建表 create table test_xz ( id varchar(20), name varchar(20), start_time varchar(20), end_time varchar(20), type varchar(20), date_1 varchar(20) )--准备数据 insert into test_xz values('1001','张三','10:30','11:30','a','2016/11/1'); insert into test_xz values('1002','张三','12:31','13:30','a','2016/11/1'); insert into test_xz values('1001','张三','10:32','11:31','a','2016/11/3'); insert into test_xz values('1003','李四','10:30','11:30','a','2016/11/4'); insert into test_xz values('1004','王五','10:30','11:30','a','2016/11/5'); insert into test_xz values('1005','王五','13:30','14:30','a','2016/11/5'); commit; --查询 select t.id, min(t1.start_time) 一号开始, max(t1.end_time) 一号结束, max(t1.type), min(t2.start_time) 二号开始, max(t2.end_time) 二号结束, max(t2.type), min(t3.start_time) 三号开始, max(t3.end_time) 三号结束, max(t3.type), min(t4.start_time) 四号开始, max(t4.end_time) 四号结束, max(t4.type), min(t5.start_time) 五号开始, max(t5.end_time) 五号结束, max(t5.type) from (select id, start_time, end_time, type from test_xz) t left join (select id, start_time, end_time, type from test_xz where date_1 = '2016/11/1') t1 --1号数据 on t.id = t1.id left join (select id, start_time, end_time, type from test_xz where date_1 = '2016/11/2') t2 on t.id = t2.id --2号数据 left join (select id, start_time, end_time, type from test_xz where date_1 = '2016/11/3') t3 on t.id = t3.id --3号数据 left join (select id, start_time, end_time, type from test_xz where date_1 = '2016/11/4') t4 on t.id = t4.id --4号数据 left join (select id, start_time, end_time, type from test_xz where date_1 = '2016/11/5') t5 on t.id = t5.id --5号数据 group by t.id order by t.id;
(select '张三' names,'10:30' btime,'11:30' etime,'a' type,'2016/11/1' rq from dual union all
select '张三' names,'12:30' btime,'13:30' etime,'a' type,'2016/11/1' rq from dual union all
select '张三' names,'10:32' btime,'11:30' etime,'a' type,'2016/11/3' rq from dual union all
select '张三' names,'18:30' btime,'19:30' etime,'a' type,'2016/11/4' rq from dual)
select a.names,a.btime,a.etime,a.type,b.btime,b.etime,b.type from
(select m.*,row_number()over(partition by names order by rq,btime) rn
from t m where rq='2016/11/1') a
full outer join
(select m.*,row_number()over(partition by names order by rq,btime) rn
from t m where rq='2016/11/3') b
on a.names = b.names and a.rn = b.rn
;
很麻烦,给你一个思路,帮你写了2天的
你必须每一天都创建一个子查询
即(select m.*,row_number()over(partition by names order by rq,btime) rn
from t m where rq='2016/11/1') 这样,然后关联第二个子查询 rq='2016/11/2'的,用全外连接关联,a.names = b.names and a.rn = b.rn匹配。另外只能2天用一个全外连接,如果你再接着连接第3天的,会出现一些问题的。这样你就需要大量的子查询和嵌套。
--建表
create table test_xz
(
id varchar(20),
name varchar(20),
start_time varchar(20),
end_time varchar(20),
type varchar(20),
date_1 varchar(20)
)--准备数据
insert into test_xz values('1001','张三','10:30','11:30','a','2016/11/1');
insert into test_xz values('1002','张三','12:31','13:30','a','2016/11/1');
insert into test_xz values('1001','张三','10:32','11:31','a','2016/11/3');
insert into test_xz values('1003','李四','10:30','11:30','a','2016/11/4');
insert into test_xz values('1004','王五','10:30','11:30','a','2016/11/5');
insert into test_xz values('1005','王五','13:30','14:30','a','2016/11/5');
commit;
--查询
select t.id,
min(t1.start_time) 一号开始,
max(t1.end_time) 一号结束,
max(t1.type),
min(t2.start_time) 二号开始,
max(t2.end_time) 二号结束,
max(t2.type),
min(t3.start_time) 三号开始,
max(t3.end_time) 三号结束,
max(t3.type),
min(t4.start_time) 四号开始,
max(t4.end_time) 四号结束,
max(t4.type),
min(t5.start_time) 五号开始,
max(t5.end_time) 五号结束,
max(t5.type) from (select id, start_time, end_time, type from test_xz) t
left join (select id, start_time, end_time, type
from test_xz
where date_1 = '2016/11/1') t1 --1号数据
on t.id = t1.id
left join (select id, start_time, end_time, type
from test_xz
where date_1 = '2016/11/2') t2 on t.id = t2.id --2号数据
left join (select id, start_time, end_time, type
from test_xz
where date_1 = '2016/11/3') t3 on t.id = t3.id --3号数据
left join (select id, start_time, end_time, type
from test_xz
where date_1 = '2016/11/4') t4 on t.id = t4.id --4号数据
left join (select id, start_time, end_time, type
from test_xz
where date_1 = '2016/11/5') t5 on t.id = t5.id --5号数据 group by t.id
order by t.id;