您现在的位置是：网站首页> 编程资料编程资料

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

2023-05-27 570人已围观

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

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

实现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)
您可能感兴趣的文章:

上一篇：oracle日志操作模式(归档模式和非归档模式的利与弊)_oracle_

下一篇：oracle—SQL技巧之(二)WMSYS.WM_CONCAT函数实现多行记录用逗号拼接在一起_oracle_

相关内容

点击排行

本栏推荐

猜你喜欢

-六神源码网