sql-server - 如何在具有连续日期和重复数据的记录中查找日期范围

Question

对此可能有任何简单的解决方案，但我看不到。我有一个包含连续日期的表，并且经常重复其中几个连续日期的关联数据:

Date       Col1  Col2
5/13/2010  1     A
5/14/2010  1     A
5/15/2010  2     B
5/16/2010  1     A
5/17/2010  1     A
5/18/2010  3     C
5/19/2010  3     C
5/20/2010  3     C

使用 MS T-SQL，我希望找到每次运行不同的 Col1 和 Col2 值的开始和结束日期:

StartDate  EndDate    Col1  Col2
5/13/2010  5/14/2010  1     A
5/15/2010  5/15/2010  2     B
5/16/2010  5/17/2010  1     A
5/18/2010  5/20/2010  3     C

假设:永远不会遗漏任何日期。 Col1 和 Col2 不为空。任何想法 - 最好不要使用游标？非常感谢，-艾伦

Answer 1

对于 SQL 2005+，我认为下面应该可以工作

WITH DATES AS
(
   SELECT COL1, COL2, DATE,
      DATEADD(DAY, -1 * ROW_NUMBER() 
      OVER(PARTITION BY COL1, COL2 ORDER BY DATE), DATE) AS GRP
   FROM YOUR_TABLE
)
SELECT COL1, COL2, MIN(DATE) AS STARTDATE, MAX(DATE) AS ENDDATE
FROM DATES
GROUP BY COL1, COL2, GRP

如果您有任何重复记录，请使用 DENSE_RANK() 而不是 ROW_NUMBER()

对于 SQL 2000，有一个子查询和一个相关联的查询。

SELECT COL1, COL2, MIN(DATE) AS STARTDATE, MAX(DATE) AS ENDDATE
FROM (SELECT COL1, COL2, DATE,
    (SELECT MIN(DATE)
     FROM YOUR_TABLE B
     WHERE B.DATE >= A.DATE AND B.COL1 = A.COL1 AND B.COL2 = A.COL2
           AND NOT EXISTS
           (SELECT *
            FROM YOUR_TABLE C
            WHERE C.COL1 = B.COL1 AND C.COL2 = B.COL2
            AND DATEDIFF(DAY, B.DATE, C.DATE) = 1)
    ) AS GRP
    FROM YOUR_TABLE A
)
GROUP BY COL1, COL2, GRP