当前位置： 移动技术网 > IT编程>数据库>Oracle > oracle—SQL技巧之(一)连续记录查询sql案例测试

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

2017年12月12日  | 移动技术网IT编程  | 我要评论

周韦彤跳水露卫生巾,mingxingyinluan,希洛苯

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

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

您可能感兴趣的文章:

如对本文有疑问，请在下面进行留言讨论，广大热心网友会与你互动！！ 点击进行留言回复

相关文章:

验证码：
最近更新的文章
大家感兴趣的文章
移动技术网