oracle—SQL技巧之(一)連續(xù)記錄查詢sql案例測試

有這樣一個需求：需要查詢出某個客戶某一年那些天是有連續(xù)辦理過業(yè)務(wù),本文給予sql實(shí)現(xiàn)語句并測試，感興趣的朋友可以了解下

需求說明：
需要查詢出某個客戶某一年那些天是有連續(xù)辦理過業(yè)務(wù)

實(shí)現(xiàn)SQL如下：
創(chuàng)建表：
代碼如下:
create table test_num
(tyear number,
tdate date);

測試數(shù)據(jù)：
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)