oracle-sql技巧-查询连续记录

发布时间:2020-03-06编辑:脚本学堂
说明:需要查询出某个客户某一年那些天是有连续办理过业务

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

实现SQL如下:

1)、创建表:

复制代码 代码如下:
create table test_num
(tyear number,
 tdate date);

2)、测试数据:

复制代码 代码如下:
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;

3)、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)