mssql sqlserver 查找连续日期及连续日期间隔天数的方法分享


摘要:
下文将分享通过sql脚本获取数据表按照一定规则排序后之间连续日期间隔的天数,如下所示:
实验环境:sqlserver 2008 R2


例:

   
create table test(keyId int identity,writeDate datetime)
go
insert into test(writeDate) values('2018.3.1')
,('2018.3.3'),('2018.3.5'),('2018.3.8')
,('2018.3.17'),('2018.3.18'),('2018.3.19')
,('2018.3.20'),('2018.3.21'),('2018.3.22')
,('2018.3.23'),('2018.3.25'),('2018.3.29')
go
  
 


---方法1:使用row_number生成相关信息
;
with tmp as 
(
select row_number() over(order by writeDate) as keyId,writeDate as d from test 
)
select b.d as [开始日期],a.d as [结束日期], 
datediff(d,b.d,a.d) as [间隔天数]
 from tmp a 
left join tmp as b on a.keyId-1 = b.keyId
where b.d is not null 
;

go


truncate table test 
drop     table test
mssql_sqlserver_日期间隔天数方法分享

mssql_sqlserver_日期间隔天数方法分享