合并重叠/连续日期范围：T-SQL实现需求及扩展场景

需求说明 #

将表中存在重叠或连续的日期范围合并为最大覆盖区间，无重叠的独立日期范围则直接保留。

表结构 #

首先是你的表定义：

CREATE TABLE [dbo].[table1] ( 
    [id] [numeric](18, 0) IDENTITY(1,1) NOT NULL, 
    [StartDate] [datetime] NOT NULL, 
    [EndDate] [datetime] NOT NULL 
)

测试数据与预期结果 #

初始场景 #

初始测试数据

INSERT INTO [dbo].[table1] VALUES 
(CAST('2013-11-01 00:00:00.000' AS DateTime), CAST('2013-11-10 00:00:00.000' AS DateTime)), 
(CAST('2013-11-05 00:00:00.000' AS DateTime), CAST('2013-11-15 00:00:00.000' AS DateTime)), 
(CAST('2013-11-10 00:00:00.000' AS DateTime), CAST('2013-11-15 00:00:00.000' AS DateTime)), 
(CAST('2013-11-10 00:00:00.000' AS DateTime), CAST('2013-11-25 00:00:00.000' AS DateTime)), 
(CAST('2013-11-26 00:00:00.000' AS DateTime), CAST('2013-11-29 00:00:00.000' AS DateTime))

初始预期结果

ID StartDate                  EndDate
--------------------------------------------------------
1  2013-11-01 00:00:00.000   2013-11-25 00:00:00.000
2  2013-11-26 00:00:00.000   2013-11-29 00:00:00.000

扩展场景（含时间间隔） #

新增测试数据

INSERT INTO [dbo].[table1] VALUES 
(CAST('2018-05-03 08:30:00.000' AS DateTime), CAST('2018-05-03 08:45:00.000' AS DateTime)), 
(CAST('2018-05-03 08:45:00.000' AS DateTime), CAST('2018-05-03 09:30:00.000' AS DateTime)), 
(CAST('2018-05-03 08:45:00.000' AS DateTime), CAST('2018-05-03 11:30:00.000' AS DateTime)), 
(CAST('2018-05-03 12:45:00.000' AS DateTime), CAST('2018-05-03 13:00:00.000' AS DateTime)), 
(CAST('2018-05-03 14:00:00.000' AS DateTime), CAST('2018-05-03 15:45:00.000' AS DateTime)), 
(CAST('2018-05-03 14:15:00.000' AS DateTime), CAST('2018-05-03 15:30:00.000' AS DateTime))

扩展场景预期结果

ID StartDate                  EndDate
--------------------------------------------------------
1  2018-05-03 08:30:00.000   2018-05-03 11:30:00.000
2  2018-05-03 12:45:00.000   2018-05-03 13:00:00.000
3  2018-05-03 14:00:00.000   2018-05-03 15:45:00.000

实现方案 #

这个方案用窗口函数精准识别需要合并的区间，核心逻辑分三步：

1. 按StartDate排序，获取前一个区间的结束时间
2. 标记每个区间是否需要开启新分组，通过累计求和生成分组ID
3. 按分组聚合，得到每个合并区间的最小开始时间和最大结束时间

具体T-SQL代码如下：

WITH CTE_Ordered AS (
    -- 按StartDate排序，获取前一条记录的EndDate
    SELECT 
        StartDate,
        EndDate,
        LAG(EndDate) OVER (ORDER BY StartDate) AS PrevEndDate
    FROM [dbo].[table1]
),
CTE_Grouped AS (
    -- 标记新分组并生成分组ID：当前区间的StartDate不早于前一个区间的EndDate（含连续时间点）则开新组
    SELECT 
        StartDate,
        EndDate,
        SUM(CASE WHEN StartDate <= DATEADD(ms, 3, PrevEndDate) THEN 0 ELSE 1 END) 
            OVER (ORDER BY StartDate ROWS UNBOUNDED PRECEDING) AS GroupID
    FROM CTE_Ordered
)
-- 按分组聚合，生成最终合并后的区间
SELECT 
    ROW_NUMBER() OVER (ORDER BY MIN(StartDate)) AS ID,
    MIN(StartDate) AS StartDate,
    MAX(EndDate) AS EndDate
FROM CTE_Grouped
GROUP BY GroupID
ORDER BY ID;

代码细节说明 #

• LAG(EndDate)：拿到当前记录的前一条区间的结束时间，用来判断是否重叠/连续
• DATEADD(ms, 3, PrevEndDate)：因为SQL Server的datetime类型精度是3毫秒，加3毫秒是为了兼容连续时间点的场景（比如前一个区间的EndDate刚好等于当前区间的StartDate）
• SUM(...) OVER (...)：通过累计求和生成分组ID，当当前区间和前一个区间不重叠也不连续时，就加1开启新分组
• ROW_NUMBER()：为合并后的区间生成新的连续ID，和你给出的预期结果格式一致

这个方案同时支持纯日期和带具体时间的场景，完全匹配你提供的测试数据和预期输出。

内容的提问来源于stack exchange，提问作者user9765321