我正在处理一个查询，该查询需要根据日期范围合并一些数据行。这些行在所有数据值中重复，但日期范围被分割。例如，表数据可能看起来像

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语句是什么？

解决方案

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

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

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

假设我们在学生的时间范围内没有差距，那将给我们带来结果。

select StudentID, min(StartDate) StartDate, max(EndDate) EndDate, Field1, Field2 
  from table
 group by StudentID, Field1, Field2

如果min()/ max()解决方案不够好(例如，日期不是连续的，并且我们想分别对不同的日期范围进行分组)，我想知道使用Oracle的START WITH和CONNECT BY子句的东西是否可以工作。当然，这不适用于每个数据库。

编辑：使另一组SQL的Access。我测试了所有这些内容，但是进行了逐段测试，因为我不知道如何在Access中一次发表多个语句。由于我也不知道如何注释，因此我们可以在下面的SQL版本中查看注释。

select 
studentid, min(startdate) as Starter, max(enddate) as Ender, field1, field2, 
max(startDate) - Min(endDate)  as MaxGap 
into tempIDs
from student 
group by studentid, field1, field2 ;  

delete from tempIDs where MaxGap > 1;

UPDATE student INNER JOIN TempIDs ON Student.studentID = TempIDS.StudentID
SET Student.StartDate = [TempIDs].[Starter],
 Student.EndDate = [TempIDs].[Ender];

我想就是这样，在SQL Server中我没有在Access中做到这一点。我还没有针对花哨的条件(例如重叠多个记录等)进行测试，但这应该可以入门。它会更新所有重复的小记录，从而将多余的内容留在数据库中。 MSDN上有一个消除重复的页面：http://support.microsoft.com/kb/139444

select 
studentid, min(startdate) as StartDate, max(enddate) as EndDate, field1, field2, 
datediff(dd, Min(endDate),max(startDate)) as MaxGap 
into #tempIDs
from #student 
group by studentid, field1, field2    

-- Update the relevant records.  Keeps two copies of the massaged record 
-- - extra will need to be deleted.

update #student 
set startdate = #TempIDS.startdate, enddate = #tempIDS.EndDate
from #tempIDS 
where #student.studentid = #TempIDs.StudentID and MaxGap < 2

下面的代码应该工作。我作了如下一些假设：日期范围没有重叠，任何字段中没有NULL值，并且给定行的开始日期总是小于结束日期。如果数据不符合这些条件，则需要调整此方法，但它应为我们指明正确的方向。

我们可以使用子查询代替视图，但这可能很麻烦，因此我使用视图使代码更清晰。

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

我们是否考虑过非股权加入？看起来像这样：

SELECT A.StudentID, A.StartDate, A.EndDate, A.Field1, A.Field2
FROM tblEnrollment AS A LEFT JOIN tblEnrollment AS B ON (A.StudentID = B.StudentID) 
   AND (A.EndDate=B.StartDate-1)
WHERE B.StudentID Is Null;

给是所有没有相应记录的记录，这些记录从第一个记录的结束日期的第二天开始。

[注意：请注意，我们只能在SQL View的Access查询设计器中编辑非等额联接-切换到Design View可能会导致联接丢失(尽管如果我们进行切换，Access会告诉我们有关问题的信息，以及是否立即切换回SQL View，我们将不会丢失)]

如果我们然后与该联合：

SELECT A.StudentID, A.StartDate, B.EndDate, A.Field1, A.Field2
FROM tblEnrollment AS A INNER JOIN tblEnrollment AS B ON (A.StudentID = B.StudentID) 
   AND (A.EndDate= B.StartDate-1)

假设一次最多有两个连续的记录，它应该可以为我们提供所需的信息。我不确定如果我们有两个以上连续的记录(它可能涉及到与StartDate-1相比，与EndDate相比，我们会怎么做)，但是这可能会使我们朝着正确的方向开始。

由汤姆·H(Tom H.)在接受的答案中提供的另一种最终查询是

SELECT
    SD.StudentID,
    SD.StartDate,
    MIN(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
GROUP BY
    SD.StudentID, SD.Field1, SD.Field2, SD.StartDate

这也适用于所有测试数据。

这是SQL(语言)中的经典问题，例如在Joe Celko的书籍" SQL for Smarties"(第23章，区域，运行，间隙，序列和系列)和他的最新著作" Thinking in Sets"(第15章)中都有介绍。

虽然在运行时使用怪异的查询来修复数据是"有趣的"，但对我来说，这是可以更好地脱机和按程序修复的情况之一(我个人会使用Excel电子表格中的公式来解决)。

重要的是放置有效的数据库约束，以防止重复出现。同样，在SQL中编写顺序约束是经典的做法：请参见Snodgrass(http://www.cs.arizona.edu/people/rts/tdbbook.pdf)。针对MS Access用户的提示：我们将需要使用CHECK约束。

以下是使用SQL Server 2005/2008语法的测试数据示例。

DECLARE @Data TABLE(
    CalendarDate datetime )

INSERT INTO @Data( CalendarDate )
-- range start
SELECT '1 Jan 2010'
UNION ALL SELECT '2 Jan 2010'
UNION ALL SELECT '3 Jan 2010'
-- range start
UNION ALL SELECT '5 Jan 2010'
-- range start
UNION ALL SELECT '7 Jan 2010'
UNION ALL SELECT '8 Jan 2010'
UNION ALL SELECT '9 Jan 2010'
UNION ALL SELECT '10 Jan 2010'

SELECT DateGroup, Min( CalendarDate ) AS StartDate, Max( CalendarDate ) AS EndDate
FROM(   SELECT NextDay.CalendarDate, 
            DateDiff( d, RangeStart.CalendarDate, NextDay.CalendarDate ) - ROW_NUMBER() OVER( ORDER BY NextDay.CalendarDate ) AS DateGroup
        FROM( SELECT Min( CalendarDate ) AS CalendarDate
                FROM @data ) AS RangeStart
            JOIN @data AS NextDay
                ON NextDay.CalendarDate >= RangeStart.CalendarDate ) A
GROUP BY DateGroup