17,086
社区成员
发帖
与我相关
我的任务
分享
with tt as(select 1 id, to_date('8:00 ','hh24:mi')starttime, to_date(' 10:00','hh24:mi')endtime, 2 h, 0 mi from dual union all
select 2 id, to_date('8:10 ','hh24:mi')starttime, to_date(' 9:10 ','hh24:mi')endtime, 1 h, 00 mi from dual union all
select 3 id, to_date('09:00','hh24:mi')starttime, to_date('10:10','hh24:mi')endtime, 1 h, 10 mi from dual union all
select 4 id, to_date('09:00','hh24:mi')starttime, to_date('11:50 ','hh24:mi')endtime, 2 h, 50 mi from dual union all
select 5 id, to_date('12:00','hh24:mi')starttime, to_date('13:00 ','hh24:mi')endtime, 1 h, 0 mi from dual union all
select 6 id, to_date('14:00','hh24:mi')starttime, to_date('16:00 ','hh24:mi')endtime, 2 h, 0 mi from dual union all
select 7 id, to_date('15:00','hh24:mi')starttime, to_date('16:00 ','hh24:mi')endtime, 1 h, 0 mi from dual )
,t1 as (select tt.*,row_number()over(order by starttime,endtime) rn from tt)
select id,starttime,endtime,h,mi,
case when time is not null then (case when time>=1 then trunc(time)||'小时' end)||(case when mod(time,1)>0 then round(mod(time,1)*60)||'分钟' end)||'/4小时'
else (case when h>0 then h||'小时' end)||(case when mi>0 then mi||'分钟' end)end 结果
from(
select id,starttime,endtime,h,mi,case when not(h+mi/60=max(h+mi/60)over(partition by flag)) then
sum(h+mi/60)over(partition by flag)-max(h+mi/60)over(partition by flag) end time from(
select a.*,connect_by_root rn flag,level from t1 a
start with not exists(select 1 from t1 where rn=a.rn-1 and endtime>=a.starttime)
connect by starttime<=prior endtime and prior rn=rn-1
)
)
ID STARTTIME ENDTIME H MI 结果
1 2009-11-1 8:00:00 2009-11-1 10:00:00 2 0 4小时10分钟/4小时
2 2009-11-1 8:10:00 2009-11-1 9:10:00 1 0 4小时10分钟/4小时
3 2009-11-1 9:00:00 2009-11-1 10:10:00 1 10 4小时10分钟/4小时
4 2009-11-1 9:00:00 2009-11-1 11:50:00 2 50 2小时50分钟
5 2009-11-1 12:00:00 2009-11-1 13:00:00 1 0 1小时
6 2009-11-1 14:00:00 2009-11-1 16:00:00 2 0 2小时
7 2009-11-1 15:00:00 2009-11-1 16:00:00 1 0 1小时/4小时
select * from t t0
where exists
( select 1 from t t1
where ( t1.starttime <= t0.starttime
and t1.endtime >= t0.starttime)
or ( t1.endtime >= t0.endtime
and t1.starttime <= t0.endtime)
or ( t1.starttime >= t0.starttime
and t1.endtime <= t0.endtime)
);