SQL經典問題：找出連續日期及連續的天數

create table tmptable(rq datetime)
go
insert tmptable values('2010.1.1')
insert tmptable values('2010.1.2')
insert tmptable values('2010.1.3')
insert tmptable values('2010.1.6')
insert tmptable values('2010.1.7')
insert tmptable values('2010.1.10')
insert tmptable values('2010.1.11')
insert tmptable values('2010.1.12')
insert tmptable values('2010.1.19')
insert tmptable values('2010.1.20')
insert tmptable values('2010.1.22')
insert tmptable values('2010.1.23')
insert tmptable values('2010.1.28')
go
---希望得到的結果
--本期起始日期 本期終止日期  持續天數 距上一期天數
--2010.1.1     2010.1.3      3        0
--2010.1.6     2010.1.7      2        3
--2010.1.10    2010.1.12     3        3
--2010.1.19    2010.1.20     2        7
--2010.1.22    2010.1.23     2        2
--2010.1.28    2010.1.28     1        5
SELECT 本期起始日期 = MIN(rq)
    , 本期終止日期 = MAX(rq)
    , 持續天數 = MAX(id1) - MIN(id1) + 1
    , 距上一期天數 = CASE a.id1 - a.id2
        WHEN -1 THEN 0
        ELSE MAX(datediff(d, rq2, rq))
    END
FROM (
    SELECT id1 = datediff(d, '2010-01-01', rq)
        , id2 = (
            SELECT COUNT(1)
            FROM tmptable
            WHERE rq <= a.rq
        ), rq2 = (
            SELECT MAX(rq)
            FROM tmptable
            WHERE rq < a.rq
        )
        , *
    FROM tmptable a
) a
GROUP BY a.id1 - a.id2

/*
本期起始日期                  本期終止日期                  持續天數        距上一期天數
----------------------- ----------------------- ----------- -----------
2010-01-01 00:00:00.000 2010-01-03 00:00:00.000 3           0
2010-01-06 00:00:00.000 2010-01-07 00:00:00.000 2           3
2010-01-10 00:00:00.000 2010-01-12 00:00:00.000 3           3
2010-01-19 00:00:00.000 2010-01-20 00:00:00.000 2           7
2010-01-22 00:00:00.000 2010-01-23 00:00:00.000 2           2
2010-01-28 00:00:00.000 2010-01-28 00:00:00.000 1           5
警告: 聚合或其他 SET 操作消除了空值。
 
(6 行受影響)
*/
       

 例2：

create table t (qdate datetime,vcode varchar(50));
insert into t values('2013-06-01','A001');
insert into t values('2013-06-02','A001');
insert into t values('2013-06-02','B001');
insert into t values('2013-06-05','A001');      

生成表如下：

按照vcode進行分組，按照qdate進行降序排列，記錄行号rn

select *,ROW_NUMBER() over (partition by vcode order by qdate desc ) as rn from [my_exercise].[dbo].[t]      

将日期減去行号，得到的結果rn連續相同時即為時間連續組

select *, (day(qdate) - row_number() over(partition by t.vcode order by t.qdate))rn from [my_exercise].[dbo].[t]      

根據vcode和rn分組，得到的count即為連續的天數

select vcode,rn,count(*)as count 
from (
    select t.*,(day(t.qdate) - row_number()over(partition by t.vcode order by t.qdate)) rn 
    from [my_exercise].[dbo].[t]) a 
group by vcode, rn      

通過having即可篩選出連續天數>=3的vcode

select vcode,rn,count(*)as count 
from (
    select t.*,(day(t.qdate) - row_number()over(partition by t.vcode order by t.qdate)) rn 
    from [my_exercise].[dbo].[t]) a 
group by vcode, rn
having count(1)>=3      

轉載于:https://www.cnblogs.com/zhaoshujie/p/9594648.html