求助 关于Oracle范围交叉和重复的查询

Question

有两张表,
第一张:
userinfo表
userid starttime
25 2010/04/19
34 2008/04/10
34 2008/09/01
第二张:
userhistory表
userid  starttime       endtime
25 2010/4/19 2010/7/31
25 2010/8/1 2010/9/30
25 2010/10/1 2010/12/31
25 2011/1/1 2012/3/31
25 2012/4/1 2012/6/30
25 2012/7/1 2012/8/31
25 2012/7/1 9999/9/9
34 2008/4/10 2008/6/30
34 2008/7/1 2008/7/31
34 2008/9/1 2008/9/30
34 2008/10/1 2008/12/31
34 2009/1/1 2009/6/30
34 2009/7/1 2009/7/31
34 2009/10/1 2009/12/31用sql查找出类似下边这样的数据
25 2012/7/1 2012/8/31
25 2012/7/1 9999/9/9
以上情况属于范围重复
34 2009/7/1 2009/7/31
34 2009/10/1 2009/12/31
以上情况属于范围中断
34 2008/7/1 2008/7/31
34 2008/9/1 2008/9/30
由于在userinfo表中存在一个starttime和第二张表一样的starttime
则此种情况不算范围中断
想找出 userhistory表中所有轨迹中断和轨迹交叉的userid我试过用
select (select count(*) from userhistory where userid = userinfo.userid) count1,
       (select count(*)
          from (select * from userhistory where userid = userinfo.userid)
         start with starttime in
                    (select starttime from userinfo where userid = userinfo.userid)
        connect by prior endtime + 1 = starttime) count2,
       userinfo.userid
  from (select distinct userid from userinfo) userinfo
这样的sql查询  查找 每个userid对应的时间轨迹总数和按照递归方式查询的轨迹总数是否一致
但因为表中有错误数据 执行过程中报出了
ORA-01436:用户数据中的connect by 循环 错误望高手指点 谢谢

Answer 1

 按大致的思路求出userhistory表中符合要求的数据 可能有简便方法 还没怎么想 关联userinfo表去除不符合的数据 应该不算难了  困了with 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