mssql sqlserver 如何获取签到日期的连续段及连续天数呢?


摘要:
下文讲述sqlserver中获取连续时间段及所对应的天数的方法分享,如下所示:
实验环境:sql server 2008 R2

create table [maomao365.com](rq datetime)
go
insert [maomao365.com] values('2020.1.1')
insert [maomao365.com] values('2020.1.2')
insert [maomao365.com] values('2020.1.3')
insert [maomao365.com] values('2020.1.6')
insert [maomao365.com] values('2020.1.7')
insert [maomao365.com] values('2020.1.10')
insert [maomao365.com] values('2020.1.11')
insert [maomao365.com] values('2020.1.12')
insert [maomao365.com] values('2020.1.19')
insert [maomao365.com] values('2020.1.20')
insert [maomao365.com] values('2020.1.22')
insert [maomao365.com] values('2020.1.23')
insert [maomao365.com] values('2020.1.28')
go

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 [maomao365.com]
WHERE rq <= a.rq ), rq2 = ( SELECT MAX(rq) FROM [maomao365.com] WHERE rq < a.rq ) , * FROM [maomao365.com] a ) a GROUP BY a.id1 - a.id2 go truncate table [maomao365.com] drop table [maomao365.com]

mssql_sqlserver_获取连续签到信息

mssql_sqlserver_获取连续签到信息