mssql sqlserver 查询一个用户连续3天都有签到的用户信息


摘要:
下文讲述sqlserver中使用sql脚本获取连续3天都有“签到”记录的用户信息,如下所示:
实验环境:sql server 2008 R2

文章开头所提到的疑问,有很多小伙伴在互联网上都见过此类信息,如:**网站的签到记录,连续签到3天奖励多少积分,在上班中,连续打卡30天,发放全勤奖,等等这样的例子,在我们的生活中,举不胜举,非常的多。
下文中举例oracle数据库中 处理此类问题的方法:

create table [maomao365.com]
(userId int ,qianDaoDate date)
go
insert into [maomao365.com]
(userId,qianDaoDate)values
(1,'2019-10-1'),
(1,'2019-10-2'),
(1,'2019-10-3'),
(1,'2019-10-4'),

(2,'2019-10-1'),
(2,'2019-10-8'),
(2,'2019-10-9'),
(2,'2019-10-11')


---1.按userId进行组内排序
select  * from 
(
 select ROW_NUMBER() 
    over(PARTITION by userId order by qianDaoDate asc )
    as keyId,userId,qianDaoDate from [maomao365.com]
) as t 

----2.将qiandaoDate 减去 KeyId 然后得到的日期进行群组,得到聚合count值

select  userId,qianDaoDate,
 dateadd(day,-keyId,qianDaoDate) as newDate 
from 
(
 select ROW_NUMBER() 
    over(PARTITION by userId order by qianDaoDate asc )
    as keyId,userId,qianDaoDate from [maomao365.com]
) as t 

---3.count值大于3则代表,为连续签到的用户
select t2.userId,count(t2.userId) as qty 
 from
  (
select  userId,qianDaoDate,
 dateadd(day,-keyId,qianDaoDate) as newDate 
from 
(
 select ROW_NUMBER() 
    over(PARTITION by userId order by qianDaoDate asc )
    as keyId,userId,qianDaoDate from [maomao365.com]
) as t ) as t2 
  group by newDate,t2.userId 
   having count(t2.userId)  >3 
 


go
truncate table [maomao365.com]
drop     table [maomao365.com]