在SQL查询中组合拆分的日期范围

Question

我正在处理一个查询，它需要根据日期范围组合一些数据行。除了拆分日期范围外，这些行在所有数据值中都是重复的。例如，表数据可能如下所示

StudentID   StartDate   EndDate     Field1  Field2
1           9/3/2007    10/20/2007  3       True
1           10/21/2007  6/12/2008   3       True
2           10/10/2007  3/20/2008   4       False
3           9/3/2007    11/3/2007   8       True
3           12/15/2007  6/12/2008   8       True

查询结果应该组合了拆分的日期范围。查询应组合间隔仅为一天的日期范围。如果有超过一天的间隔，则不应合并行。没有拆分日期范围的行应该保持不变。结果将如下所示

StudentID   StartDate   EndDate     Field1  Field2
1           9/3/2007    6/12/2008   3       True
2           10/10/2007  3/20/2008   4       False
3           9/3/2007    11/3/2007   8       True
3           12/15/2007  6/12/2008   8       True

这个查询的SELECT语句是什么？

Answer 1

下面的代码应该可以工作。我做了以下几个假设:日期范围没有重叠，任何字段中都没有空值，给定行的开始日期始终小于结束日期。如果您的数据不符合这些标准，您将需要调整此方法，但它应该为您指明正确的方向。

您可以使用子查询而不是视图，但这可能会很麻烦，所以我使用视图使代码更清晰。

CREATE VIEW dbo.StudentStartDates
AS
    SELECT
        S.StudentID,
        S.StartDate,
        S.Field1,
        S.Field2
    FROM
        dbo.Students S
    LEFT OUTER JOIN dbo.Students PREV ON
        PREV.StudentID = S.StudentID AND
        PREV.Field1 = S.Field1 AND
        PREV.Field2 = S.Field2 AND
        PREV.EndDate = DATEADD(dy, -1, S.StartDate)
    WHERE PREV.StudentID IS NULL
GO

CREATE VIEW dbo.StudentEndDates
AS
    SELECT
        S.StudentID,
        S.EndDate,
        S.Field1,
        S.Field2
    FROM
        dbo.Students S
    LEFT OUTER JOIN dbo.Students NEXT ON
        NEXT.StudentID = S.StudentID AND
        NEXT.Field1 = S.Field1 AND
        NEXT.Field2 = S.Field2 AND
        NEXT.StartDate = DATEADD(dy, 1, S.EndDate)
    WHERE NEXT.StudentID IS NULL
GO


SELECT
    SD.StudentID,
    SD.StartDate,
    ED.EndDate,
    SD.Field1,
    SD.Field2
FROM
    dbo.StudentStartDates SD
INNER JOIN dbo.StudentEndDates ED ON
    ED.StudentID = SD.StudentID AND
    ED.Field1 = SD.Field1 AND
    ED.Field2 = SD.Field2 AND
    ED.EndDate > SD.StartDate AND
    NOT EXISTS (SELECT * FROM dbo.StudentEndDates ED2 WHERE ED2.StudentID = SD.StudentID AND ED2.Field1 = SD.Field1 AND ED2.Field2 = SD.Field2 AND ED2.EndDate < ED.EndDate AND ED2.EndDate > SD.StartDate)
GO

Answer 2

根据我的经验，我必须在后处理中组合范围(不是在SQL中，而是在我的脚本中)。我不确定SQL是否能做到这一点，特别是因为您永远不能确切知道在任何特定情况下需要链接多少个日期范围。如果能做到这一点，我也很想知道。

编辑：我的答案是假设每个学生都有多个日期范围，而不仅仅是开始和结束。如果您只有一个日期范围，没有差距，那么其他提到的解决方案是可行的。

Answer 3

SELECT StudentID, MIN(startdate) AS startdate, MAX(enddate), field1, field2
FROM tablex
GROUP BY StudentID, field1, field2

假设学生的时间范围不是差距，这会给你带来结果。