sql - 根据日期组合 SQL 记录

Question

我有下表:

startDate   endDate
----------------------
01-01-2014  01-07-2014
01-08-2014  01-14-2014
01-15-2014  01-21-2014

01-28-2014  02-03-2014
02-04-2014  02-10-2014

我想将日期捆绑在一起以尽量减少记录数量。输出结果应如下所示:

startDate   endDate
----------------------
01-01-2014  01-21-2014
01-28-2014  02-10-2014

如果一个记录的 startDate 比另一个记录的 endDate 晚一天，则日期可以链接在一起。

不使用游标可以实现吗？

Answer 1

正如评论中提到的，您需要 Recursive CTE 加上 Window Function 来聚合连续天数

;WITH cte
     AS (SELECT StartDate,
                EndDate
         FROM   yourtable
         UNION ALL
         SELECT a.StartDate,
                b.EndDate
         FROM   cte a
                JOIN yourtable b
                  ON Dateadd(DAY, 1, a.EndDate) = b.StartDate),
     cte1
     AS (SELECT StartDate,
                EndDate,
                Row_number()
                  OVER(
                    partition BY EndDate
                    ORDER BY StartDate ASC) AS rn
         FROM   cte)
SELECT StartDate,
       Max(EndDate) AS EndDate
FROM   cte1 a
WHERE  a.rn = 1
GROUP  BY StartDate
ORDER  BY EndDate 

SQLFIDDLE DEMO