如何基于最大最小值将SQL Server中连续同组记录合并为单行

核心思路 #

采用经典的*间隙与孤岛（Gaps and Islands）*窗口函数方案，纯声明式Set-based操作，性能远高于游标/循环，适合千万级以下的大数据量处理。

前置准备 #

假设你的员工状态表名为EmployeeStatusHistory，建议先创建如下索引提升性能：

CREATE NONCLUSTERED INDEX IX_EmployeeStatusHistory_EmpID_SDate_EDate
ON EmployeeStatusHistory (员工ID, 开始日期, 结束日期)
INCLUDE (员工状态, 记录ID);

合并SQL脚本 #

WITH Preprocess AS (
    -- 预处理：标记每个员工同状态连续分组的起点
    SELECT 
        记录ID,
        员工ID,
        开始日期,
        结束日期,
        员工状态,
        -- 状态和上一条不同，或上一条结束日期不等于当前开始日期，标记为新分组起点
        CASE WHEN LAG(员工状态) OVER (PARTITION BY 员工ID ORDER BY 开始日期, 结束日期) = 员工状态
                AND LAG(结束日期) OVER (PARTITION BY 员工ID ORDER BY 开始日期, 结束日期) = 开始日期
             THEN 0 ELSE 1 END AS IsNewGroup
    FROM EmployeeStatusHistory
),
GroupMark AS (
    -- 累计求和生成每个连续分组的唯一ID
    SELECT 
        *,
        SUM(IsNewGroup) OVER (PARTITION BY 员工ID ORDER BY 开始日期, 结束日期 ROWS UNBOUNDED PRECEDING) AS GroupID
    FROM Preprocess
)
-- 聚合得到合并后的结果
SELECT 
    MIN(记录ID) AS 记录ID, -- 取分组内最小记录ID，和示例输出要求一致
    员工ID,
    MIN(开始日期) AS 开始日期,
    -- NULL表示当前有效，MAX逻辑会保留NULL值
    CASE WHEN MAX(ISNULL(结束日期, '9999-12-31')) = '9999-12-31' THEN NULL ELSE MAX(结束日期) END AS 结束日期,
    员工状态
INTO #MergedStatus
FROM GroupMark
GROUP BY 员工ID, 员工状态, GroupID;

-- 替换原表（建议在事务中执行，失败可回滚）
BEGIN TRANSACTION;
BEGIN TRY
    TRUNCATE TABLE EmployeeStatusHistory;
    INSERT INTO EmployeeStatusHistory (记录ID, 员工ID, 开始日期, 结束日期, 员工状态)
    SELECT 记录ID, 员工ID, 开始日期, 结束日期, 员工状态 FROM #MergedStatus;
    COMMIT TRANSACTION;
    PRINT '数据合并完成';
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    PRINT '数据合并失败，已回滚：' + ERROR_MESSAGE();
END CATCH;

DROP TABLE IF EXISTS #MergedStatus;

效果验证 #

对示例中的原始数据运行脚本后，输出结果如下：

完全符合需求，同时兼容重叠、断层的异常数据场景。

注意事项 #

• 首次运行前请务必备份原表，避免数据损失。
• 如果不需要保留原始记录ID，可以省略MIN(记录ID)的逻辑，或者用自增主键生成新的ID。
• 脚本中TRUNCATE操作会清空原表，如果你需要保留原始数据，可以将合并后的结果写入新表做校验后再切换使用。

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