【SQL笔记】查询连续日期

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://bbs.csdn.net/topics/390506222?page=1