SQL检查日期值是否连续5天

Question

我有一个日期列包含日期值，我想看看是否所有日期值都包含5天，例如

2019-03-15。。。2021-06-012021-06-022021-06-032021-06-042021-06-05。。。。2021-07-01

在这种情况下返回真实

唯一的问题是语法必须适用于SQL Server 2008 R2版本的

Answer 1

您可以减去一个枚举值，该值对于相邻日期是常量。然后进行汇总和计数。

因此，要获得所有5或更大的序列：

select min(date), max(date), count(*)
from (select t.*, row_number() over (order by date) as seqnum
from t
) t
group by dateadd(day, - seqnum, date)
having count(*) >= 5;

如果要返回'true'/'false'值：

select (case when exists (select min(date), max(date), count(*)
from (select t.*, row_number() over (order by date) as seqnum
from t
) t
group by dateadd(day, - seqnum, date)
having count(*) >= 5
)
then 'true' else 'false'
end) as flag