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

2019/6/30 21:19:57

编程Tag： sql查询连续记录

本文主要是介绍oracle—SQL技巧之(一)连续记录查询sql案例测试，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

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

实现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—SQL技巧之(一)连续记录查询sql案例测试的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！

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

相关编程文章