请问一个判断时间重复的算法

Question

问题是这样的:有N条记录,每条记录里面包含一个开始时间段和一个结束时间段, 
例: 
序号 开始时间  结束时间  时间差(小时)  时间差(分钟)    最后结果 
1    8:00      10:00    2            0              4小时10分/4小时 
2    8:10      9:10    1            00              4小时10分/4小时 
3    09:00    10:`10  1            10              4小时10分/4小时 
4    09:00    11:50    2            50              2小时50分 
5    12:00    13:00    1            0              1小时 
6    14:00    16:00    2            0              2小时 
7    15:00    16:00    1            0              1小时/4小时    
第1,2,3,4是重复的时间. 
现在的要求是要找出重复的时间,然后将一个时间差最大的值上面的例子是第4条,然后把结果填上去直接是时间差的结果, 
其他时间全部直接累加然后除以4小时作为结果. 其他如果不重复的直接是时间差作为结果就可以了,如第5条. 
第6和7是重复的时间. 
请问一下怎样找出这些重复时间呢,有没有比较好的算法?谢谢 
 
 
 

Answer 1

每条记录开始时间和结束时间的时间段与其他记录有重合的，就算为重复记录。如果是重复记录，那么再看时间差，取差最大的那条，其他重复记录则除以4楼上的大家可以再仔细看看，如果只是找出重复的记录还是很简单的，我想难的是把这些重复的时间分组。下面是找出重复时间的sql，效率不好，期待后面更好的方法。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)
);

Answer 2

嗯，掉了一种or     (    t1.starttime <= t0.starttime
          and t1.endtime   >= t0.endtime)

Answer 3

噢，实际上
t1.starttime <= t0.starttime
and t1.endtime   >= t0.starttime
已经包括了这种情况。

Answer 4

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小时

Answer 5

wildwave已经实现功能了，他这里的start with 用的比较精妙，是找出有重复的记录中的第一条记录。后面是用connect by 来实现连接，并进行分组 就比较简单了。