oracle—SQL技巧之(一)连续记录查询sql案例测试
网络编程 2021-07-05 16:08www.168986.cn编程入门
有这样一个需求:需要查询出某个客户某一年那些天是有连续办理过业务,本文给予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)
需要查询出某个客户某一年那些天是有连续办理过业务
实现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)
编程语言
- 机械手焊接机器人编程 机械手焊接设备编程
- vb数据库编程实例,vb做数据库
- tiobe世界编程语言排行榜,全球编程语言排行
- 成都java编程培训 成都it编程培训中心
- 编程基础培训,编程基础知识培训
- flash游戏编程基础教程,flash游戏开发教程
- 不会英语能学编程吗 不会英语可以学程序员吗
- 爱心代码编程C语言 爱心代码编程c语言公式
- c语言编程学习入门 c语言编程快速入门
- 西门子plc编程培训 西门子plc编程培训班
- 哪里可以学编程 台州哪里可以学编程
- 少儿编程哪个好 少儿编程哪个品牌好
- 编程是什么意思,图形编程是什么意思
- c语言编程软件下载,c语言编程软件在哪下载
- java编程软件下载,java编程下载手机版
- ug编程培训学校,ug编程培训教程