使用 SQL 检测连续的日期范围
我想填充需要开始和结束日期信息的日历对象.我有一个包含日期序列的列.有些日期是连续的(有一天的差异)，有些则不是.
I want to fill the calendar object which requires start and end date information. I have one column which contains a sequence of dates. Some of the dates are consecutive (have one day difference) and some are not.
InfoDate
2013-12-04 consecutive date [StartDate]
2013-12-05 consecutive date
2013-12-06 consecutive date [EndDate]
2013-12-09 [startDate]
2013-12-10 [EndDate]
2014-01-01 [startDate]
2014-01-02
2014-01-03 [EndDate]
2014-01-06 [startDate]
2014-01-07 [EndDate]
2014-01-29 [startDate]
2014-01-30
2014-01-31 [EndDate]
2014-02-03 [startDate]
2014-02-04 [EndDate]
我想选择每个连续日期范围的开始和结束日期(块中的第一个和最后一个).
I want to pick each consecutive dates range’s start and end date (the first one and the last one in the block).
StartDate EndDate
2013-12-04 2013-12-06
2013-12-09 2013-12-10
2014-01-01 2014-01-03
2014-01-06 2014-01-07
2014-01-29 2014-01-31
2014-02-03 2014-02-04
我只想用 SQL 解决问题.
I want to solve the problem using SQL only.
推荐答案
不需要连接或递归 CTE.标准的间隙和岛解决方案是按(值减去 row_number)分组，因为它在连续序列中是不变的.开始和结束日期只是组的 MIN() 和 MAX().
No joins or recursive CTEs needed. The standard gaps-and-island solution is to group by (value minus row_number), since that is invariant within a consecutive sequence. The start and end dates are just the MIN() and MAX() of the group.
WITH t AS (
SELECT InfoDate d,ROW_NUMBER() OVER(ORDER BY InfoDate) i
FROM @d
GROUP BY InfoDate
)
SELECT MIN(d),MAX(d)
FROM t
GROUP BY DATEDIFF(day,i,d)
相关文章