oracle 时间交叉,关于Oracle范围交叉和重复的查询

SQL codewith userhistory as

(

select 25 userid,date'2012-04-01' starttime,date'2012-06-30' endtime from dual

union all

select 25 userid,date'2012-07-01' starttime,date'2012-08-30' endtime from dual

union all

select 25 userid,date'2012-07-01' starttime,date'9999-09-09' endtime from dual

union all

select 34 userid,date'2008-04-10' starttime,date'2008-06-30' endtime from dual

union all

select 34 userid,date'2008-07-01' starttime,date'2008-07-31' endtime from dual

union all

select 34 userid,date'2008-09-01' starttime,date'2008-09-30' endtime from dual

union all

select 34 userid,date'2008-10-01' starttime,date'2008-12-31' endtime from dual

union all

select 34 userid,date'2009-01-01' starttime,date'2009-06-30' endtime from dual

union all

select 34 userid,date'2009-07-01' starttime,date'2009-07-31' endtime from dual

union all

select 34 userid,date'2009-10-01' starttime,date'2009-12-31' endtime from dual

)

select t1.userid,t1.starttime,t1.endtime

from (select rownum rn,a.* from userhistory a) t1,

(

select rn,userid,starttime,endtime

from

(

select rownum rn,userid,starttime,endtime,lag(endtime) over(partition by userid order by rownum) t_time

from userhistory

)

where starttime-t_time<>1

) t2

where t1.rn=t2.rn or t1.rn+1 = t2.rn

order by t1.userid,t1.starttime

userid starttime endtime

----------------------------------------------

1 25 2012/7/1 2012/8/30

2 25 2012/7/1 9999/9/9

3 34 2008/7/1 2008/7/31

4 34 2008/9/1 2008/9/30

5 34 2009/7/1 2009/7/31

6 34 2009/10/1 2009/12/31