SQL技巧之查询连续的记录

来源：互联网发布：python小游戏源代码编辑：程序博客网时间：2024/04/29 20:03

http://oracle.chinaitlab.com/PLSQL/913184.html

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;

SELECT * FROM test_num ORDER BY TYEAR, TDATE;

　　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);