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

求助 关于Oracle范围交叉和重复的查询
有两张表,
第一张:
userinfo表
useridstarttime
252010/04/19
342008/04/10
342008/09/01
第二张:
userhistory表
userid starttime endtime
252010/4/192010/7/31
252010/8/12010/9/30
252010/10/12010/12/31
252011/1/12012/3/31
252012/4/12012/6/30
252012/7/12012/8/31
252012/7/19999/9/9
342008/4/102008/6/30
342008/7/12008/7/31
342008/9/12008/9/30
342008/10/12008/12/31
342009/1/12009/6/30
342009/7/12009/7/31
342009/10/12009/12/31

用sql查找出类似下边这样的数据
252012/7/12012/8/31
252012/7/19999/9/9
以上情况属于范围重复
342009/7/12009/7/31
342009/10/12009/12/31
以上情况属于范围中断
342008/7/12008/7/31
342008/9/12008/9/30
由于在userinfo表中存在一个starttime和第二张表一样的starttime
则此种情况不算范围中断
想找出 userhistory表中所有轨迹中断和轨迹交叉的userid

我试过用

SQL code

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 循环 错误

望高手指点 谢谢


[解决办法]
按大致的思路求出userhistory表中符合要求的数据 可能有简便方法 还没怎么想 关联userinfo表去除不符合的数据 应该不算难了 困了

SQL code

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.endtimefrom (select rownum rn,a.* from userhistory a) t1,(select rn,userid,starttime,endtimefrom     (    select rownum rn,userid,starttime,endtime,lag(endtime) over(partition by userid order by rownum) t_time    from userhistory    ) where starttime-t_time<>1 ) t2where t1.rn=t2.rn or t1.rn+1 = t2.rnorder by t1.userid,t1.starttime    userid   starttime    endtime----------------------------------------------1    25    2012/7/1    2012/8/302    25    2012/7/1    9999/9/93    34    2008/7/1    2008/7/314    34    2008/9/1    2008/9/305    34    2009/7/1    2009/7/316    34    2009/10/1    2009/12/31 

 

查看更多 下一篇