提供了编程的基础技术教程

zl程序教程

您现在的位置是：首页 >  数据库

当前栏目

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

Oracle案例测试SQL 查询 技巧 记录 连续
2023-06-13 09:14:44 时间
需求说明：
需要查询出某个客户某一年那些天是有连续办理过业务

实现SQL如下：
创建表：
复制代码代码如下:

createtabletest_num
(tyearnumber,
tdatedate);

测试数据：
insertintotest_num
select2014,trunc(sysdate)-1fromdualunionall
select2014,trunc(sysdate)-002fromdualunionall
select2014,trunc(sysdate)-003fromdualunionall
select2014,trunc(sysdate)-004fromdualunionall
select2014,trunc(sysdate)-005fromdualunionall
select2014,trunc(sysdate)-007fromdualunionall
select2014,trunc(sysdate)-008fromdualunionall
select2014,trunc(sysdate)-009fromdualunionall
select2013,trunc(sysdate)-120fromdualunionall
select2013,trunc(sysdate)-121fromdualunionall
select2013,trunc(sysdate)-122fromdualunionall
select2013,trunc(sysdate)-124fromdualunionall
select2013,trunc(sysdate)-125fromdualunionall
select2013,trunc(sysdate)-127fromdualunionall
select2015,trunc(sysdate)-099fromdualunionall
select2015,trunc(sysdate)-100fromdualunionall
select2015,trunc(sysdate)-101fromdualunionall
select2015,trunc(sysdate)-102fromdualunionall
select2015,trunc(sysdate)-104fromdualunionall
select2015,trunc(sysdate)-105fromdual;

写SQL:
复制代码代码如下:

SELECTTYEAR,MIN(TDATE)ASSTARTDATE,MAX(TDATE),COUNT(TYEAR)ASENDNUM
FROM(SELECTA.*,A.TDATE-ROWNUMASGNUM
FROM(SELECT*FROMTEST_NUMORDERBYTYEAR,TDATE)A)
GROUPBYTYEAR,GNUM
ORDERBYTYEAR,MIN(TDATE)

相关文章