手机
当前位置:查字典教程网 >编程开发 >Oracle教程 >oracle—SQL技巧之(一)连续记录查询sql案例测试
oracle—SQL技巧之(一)连续记录查询sql案例测试
摘要:需求说明:需要查询出某个客户某一年那些天是有连续办理过业务实现SQL如下:创建表:复制代码代码如下:createtabletest_num(...

需求说明:

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

实现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中从指定日期中获取月份或者部分数据

oracle开发技术:col格式化方法

oracle数据库常用的99条查询语句

Oracle对两个数据表交集的查询

Oracle 当前用户下所有表的记录总数

Oracle数据库并行查询出错的解决方法

解决oracle用户连接失败的解决方法

oracle 合并查询 事务 sql函数小知识学习

Oracle并行操作之并行查询实例解析

oracle sql 去重复记录不用distinct如何实现

精品推荐
分类导航