SQL无循环合并连续员工合同记录的实现方案

场景说明 #

已有临时表#Employee_Contract_Dates存储员工合同的起止日期，且已生成带分组行号的临时表#ContractSource（按员工ID分组、合同开始日期排序后的行号）。当前仅能合并前两行连续日期的合同，需要实现无需WHILE循环的方式，将所有连续日期的合同合并为单行，输出员工的连续合同段信息（包含员工ID、连续合同起始日期、结束日期、合同时长）。

测试数据准备 #

先创建测试用的临时表并插入模拟数据，方便验证方案：

-- 创建原始合同表
CREATE TABLE #Employee_Contract_Dates (
    EmployeeID INT,
    ContractStart DATE,
    ContractEnd DATE
);

-- 插入模拟数据：员工1有3段连续合同，员工2有2段不连续合同
INSERT INTO #Employee_Contract_Dates VALUES
(1, '2020-01-01', '2020-12-31'),
(1, '2021-01-01', '2021-12-31'),
(1, '2022-01-01', '2022-12-31'),
(2, '2020-01-01', '2020-06-30'),
(2, '2020-09-01', '2020-12-31'),
(2, '2021-01-01', '2021-12-31');

-- 生成带行号的合同源表（按员工ID分组，合同开始日期排序）
CREATE TABLE #ContractSource (
    EmployeeID INT,
    ContractStart DATE,
    ContractEnd DATE,
    RowNum INT
);

INSERT INTO #ContractSource
SELECT 
    EmployeeID,
    ContractStart,
    ContractEnd,
    ROW_NUMBER() OVER (PARTITION BY EmployeeID ORDER BY ContractStart) AS RowNum
FROM #Employee_Contract_Dates;

方案1：Gaps and Islands经典方案（推荐，性能更优） #

利用窗口函数识别"连续岛"，无需递归，是处理这类连续区间合并的标准方案：

WITH ContractGroups AS (
    SELECT 
        EmployeeID,
        ContractStart,
        ContractEnd,
        -- 识别连续分组：当前合同开始日期 = 上一个合同结束日期+1天，则属于同一组
        SUM(CASE WHEN DATEADD(DAY, 1, LAG(ContractEnd) OVER (PARTITION BY EmployeeID ORDER BY ContractStart)) = ContractStart THEN 0 ELSE 1 END) 
        OVER (PARTITION BY EmployeeID ORDER BY ContractStart) AS GroupID
    FROM #ContractSource
)
SELECT 
    EmployeeID,
    MIN(ContractStart) AS ContinuousStart, -- 组内最早的开始日期
    MAX(ContractEnd) AS ContinuousEnd,     -- 组内最晚的结束日期
    DATEDIFF(DAY, MIN(ContractStart), MAX(ContractEnd)) + 1 AS TotalDays -- 计算总时长（含首尾）
FROM ContractGroups
GROUP BY EmployeeID, GroupID
ORDER BY EmployeeID, ContinuousStart;

逻辑说明 #

1. 用LAG()窗口函数获取当前行的上一行合同结束日期，判断当前合同是否与上一合同连续（即当前开始日期 = 上一结束日期+1天）
2. 用SUM() OVER()累加分组标识，每遇到不连续的合同就新建一个分组
3. 最后按员工ID和分组ID聚合，得到每个连续合同段的起止日期和总时长

方案2：递归CTE方案 #

如果必须使用递归方式，可通过递归CTE逐步合并连续的合同记录：

WITH RecursiveContracts AS (
    -- 锚点成员：取每个员工的第一条合同记录
    SELECT 
        EmployeeID,
        ContractStart,
        ContractEnd,
        RowNum
    FROM #ContractSource
    WHERE RowNum = 1

    UNION ALL

    -- 递归成员：匹配当前合同的下一条连续合同，合并起止日期
    SELECT 
        rc.EmployeeID,
        rc.ContractStart, -- 保留最早的开始日期
        cs.ContractEnd,   -- 更新为最新的结束日期
        cs.RowNum
    FROM RecursiveContracts rc
    JOIN #ContractSource cs
        ON rc.EmployeeID = cs.EmployeeID
        AND cs.RowNum = rc.RowNum + 1
        AND DATEADD(DAY, 1, rc.ContractEnd) = cs.ContractStart -- 连续日期判断
)
-- 取每个员工递归后的最后一条记录（即合并后的完整连续段）
SELECT 
    EmployeeID,
    ContractStart AS ContinuousStart,
    ContractEnd AS ContinuousEnd,
    DATEDIFF(DAY, ContractStart, ContractEnd) + 1 AS TotalDays
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY EmployeeID ORDER BY RowNum DESC) AS LastRow
    FROM RecursiveContracts
) t
WHERE LastRow = 1
ORDER BY EmployeeID;

逻辑说明 #

1. 锚点成员先获取每个员工的第一条合同作为初始连续段
2. 递归成员不断匹配下一条连续的合同，将当前连续段的结束日期更新为下一条合同的结束日期
3. 最后通过子查询取每个员工递归后的最后一行，即为合并后的完整连续合同段

注意事项 #

• 若合同存在重叠（而非严格连续），需调整连续判断逻辑（比如cs.ContractStart <= DATEADD(DAY, 1, rc.ContractEnd)）
• 大数据量下，Gaps and Islands方案的性能远优于递归CTE，优先推荐前者

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