oracle 时间连续性判断

1:模拟一张表来查询日期范围的连续性

create table myrecords
(
      id         number,  
      start_date number,   --开始时间
      end_date   number   --结束时间
);
insert into myrecords values (1,20100101,20101231);
insert into myrecords values (1,20100101,20110231);
insert into myrecords values (1,20100202,20100303);
insert into myrecords values (2,20100101,20101001);
insert into myrecords values (2,20100501,20101231);
insert into myrecords values (3,20100101,20100301);
insert into myrecords values (3,20100201,20100506);
insert into myrecords values (3,20100401,20101231);
insert into myrecords values (4,20100101,20100501);
insert into myrecords values (4,20100601,20101231);
insert into myrecords values (4,20100601,20101231);

SQL> select * from myrecords order by id,start_date,end_date;
 
        ID START_DATE   END_DATE
---------- ---------- ----------
         1   20100101   20101231
         1   20100101   20110231
         1   20100202   20100303
         2   20100101   20101001
         2   20100501   20101231
         3   20100101   20100301
         3   20100201   20100506
         3   20100401   20101231
         4   20100101   20100501
         4   20100601   20101231

 

2:要求查出在20100101到20101231 这段时间内连续的用户id,也就是上面记录中的1 , 2 , 3 都是成功的,4 的日期中间断开了..

--sql 思想.. 根据id分组,日期排序,判断每一行的日期,与上一个日期是否是连续的,若不连续则置标志break为1,外层过滤掉break为1的

select id, min(start_date), max(end_date)
  from (select id,
               start_date,
               end_date,
               (case
                 when start_date > max(end_date + 1)
                  over(partition by id order by start_date,
                           end_date rows between unbounded preceding and 1
                           preceding) then
                  1
                 else
                  0
               end) break
          from myrecords
         where 20100101 between start_date and end_date
            or start_date between 20100101 and 20101231)
 where break = 0
 group by id
having 20100101 between min(start_date) and max(start_date) and 20101231 between min(end_date) and max(end_date);

        ID MIN(START_DATE) MAX(END_DATE)
---------- --------------- -------------
         1        20100101      20110231
         2        20100101      20101231
         3        20100101      20101231

从结果可以看出,满足题目要求的员工id,12,3; 同时查询出了 满足这段时间的最大的连续日期范围;

参考from : http://www.itpub.net/thread-1223937-3-285.html