Oracle--SQL技巧之一(查询连续的记录)

需求说明：

   需要查询出某个客户某一年那些天是有连续办理过业务

 

实现SQL如下：

 

   创建表：

 

create table test_num

(tyear number,

 tdate date);

 

   测试数据：

 

insert into test_num

select 2014,trunc(sysdate)-1 from dual union all 

select 2014,trunc(sysdate)-002 from dual union all 

select 2014,trunc(sysdate)-003 from dual union all 

select 2014,trunc(sysdate)-004 from dual union all 

select 2014,trunc(sysdate)-005 from dual union all 

select 2014,trunc(sysdate)-007 from dual union all 

select 2014,trunc(sysdate)-008 from dual union all 

select 2014,trunc(sysdate)-009 from dual union all 

select 2013,trunc(sysdate)-120 from dual union all 

select 2013,trunc(sysdate)-121 from dual union all 

select 2013,trunc(sysdate)-122 from dual union all 

select 2013,trunc(sysdate)-124 from dual union all 

select 2013,trunc(sysdate)-125 from dual union all 

select 2013,trunc(sysdate)-127 from dual union all 

select 2015,trunc(sysdate)-099 from dual union all 

select 2015,trunc(sysdate)-100 from dual union all 

select 2015,trunc(sysdate)-101 from dual union all 

select 2015,trunc(sysdate)-102 from dual union all 

select 2015,trunc(sysdate)-104 from dual union all 

select 2015,trunc(sysdate)-105 from dual;

 

写SQL:

SELECT TYEAR, MIN(TDATE) AS STARTDATE, MAX(TDATE), COUNT(TYEAR) AS ENDNUM

  FROM (SELECT A.*, A.TDATE - ROWNUM AS GNUM

          FROM (SELECT * FROM TEST_NUM ORDER BY TYEAR, TDATE) A)

 GROUP BY TYEAR, GNUM

 ORDER BY TYEAR, MIN(TDATE)

运用连续记录之间相差都是1，因此连续记录用日期-rownum后的差值都是一样的，这样就可以得出连续记录。

转自：http://cache.baiducontent.com/c?m=9d78d513d99e01fc09b3c3690d67c0161943f7682ba1d3020ca08449e3732a42501592ac51200770d9d27d1716df384b9bf52103371450c68f8fc814d2e1d46e6d9f26476d01c30457c419d8cb17639c37902db8f246faeca374c0fe8192884953c850047883ed8850021fcb6ae71446b9e0c616&p=882a9643d58116eb00be9b7c5b0dcf&newp=8b2a97178f8211a05beb972f5a5cbb231610db2151d4da162d8dd2&user=baidu&fm=sc&query=oracle+%B2%E9%D1%AF%C1%AC%D0%F8%BC%C7%C2%BC&qid=&p1=5