oracle—SQL技巧之(一)连续记录查询sql案例测试

发布时间：2020-12-12 16:53:53 所属栏目：百科来源：网络整理

导读：需求说明：需要查询出某个客户某一年那些天是有连续办理过业务实现SQL如下：创建表：代码如下:create table test_num (tyear number, tdate date); 测试数据： insert into test_num select 2014,trunc(sysdate)-1 from dual union all select 2014,t

需求说明

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

实现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)

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!