MSSQL中如何用表2时间段切割表1？ID A需返回两条结果

场景回顾 #

现有两张带时间段的表：

表1（ID, begin_date, end_date） #

A - 2023-01-01 - 2023-10-31
B - 2023-01-01 - 2023-03-31

表2（ID, begin_date, end_date） #

A - 2023-02-01 - 2023-09-30
B - 2023-03-01 - 2023-04-30

需要把表2的时间段从表1对应ID的时间段中切割掉，最终ID A返回两段剩余时间，ID B返回一段。

核心解决思路 #

本质是计算两个时间段的差集，根据表2时间段在表1中的位置，分情况生成剩余时间段：

• 若表1开始时间早于表2开始时间，生成表1开始到表2开始前一天的时间段
• 若表1结束时间晚于表2结束时间，生成表2结束后一天到表1结束的时间段
  用UNION ALL合并这两种情况的结果，自动得到符合要求的多条记录。

具体SQL代码 #

-- 替换为你的实际表名和字段名（注意避免用begin/end这类关键字）
SELECT 
    t1.ID,
    t1.begin_date AS new_begin,
    DATEADD(day, -1, t2.begin_date) AS new_end
FROM Table1 t1
JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t1.begin_date < t2.begin_date

UNION ALL

SELECT 
    t1.ID,
    DATEADD(day, 1, t2.end_date) AS new_begin,
    t1.end_date AS new_end
FROM Table1 t1
JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t1.end_date > t2.end_date

代码说明 #

• 第一个查询块处理前半段剩余时间：只有当表1的开始时间比表2早时，才会生成这条记录（对应ID B的结果、ID A的第一段结果）
• 第二个查询块处理后半段剩余时间：只有当表1的结束时间比表2晚时，才会生成这条记录（对应ID A的第二段结果）
• UNION ALL会保留所有符合条件的结果，不会去重，刚好满足ID A返回两条的需求

注意事项 #

• 日期字段建议用DATE类型，避免时间部分导致的边界错误
• 如果存在一个表1ID对应多个表2时间段的情况，需要用递归CTE来处理多次切割，但当前一对一场景用上面的代码足够
• 边界日期计算要准确，比如用DATEADD(day, -1, t2.begin_date)确保和表2时间段没有重叠

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