如何将部门属性生效日期记录合并到员工生效日期主表（SQL）

核心思路是通过时间区间重叠匹配，将员工在部门的任职时间段与对应部门的属性生效时间段做关联，提取两者的重叠区间作为最终有效记录，同时关联部门属性。

表定义（SQL引用别名） #

• 员工部门历史表：emp_dept_history
• 部门属性历史表：dept_attr_history

合并SQL示例 #

SELECT
    edh.emp_id,
    edh.dept,
    -- 取两个区间的较晚起始日期作为合并后记录的生效开始时间
    GREATEST(edh.start_date, dah.start_date) AS effective_start_date,
    -- 取两个区间的较早结束日期作为合并后记录的生效结束时间
    LEAST(edh.end_date, dah.end_date) AS effective_end_date,
    dah.location
FROM emp_dept_history edh
JOIN dept_attr_history dah
    ON edh.dept = dah.dept
    -- 核心条件：判断两个时间区间是否存在重叠
    AND edh.start_date <= dah.end_date
    AND edh.end_date >= dah.start_date
ORDER BY edh.emp_id, effective_start_date;

逻辑说明 #

1. 部门关联：通过dept字段匹配两张表，确保是同一部门的历史数据。
2. 区间重叠判断：edh.start_date <= dah.end_date AND edh.end_date >= dah.start_date是时间区间重叠的通用判断条件，覆盖包含、交叉、首尾相接等所有重叠场景。
3. 有效区间计算：用GREATEST和LEAST函数计算出员工任职与部门属性生效的重叠时间段，这个时间段就是两者状态同时有效的区间。

测试数据适配说明 #

你提供的员工任职时间段均在2023年，而部门属性历史的时间段都截止到2021年，两者无时间重叠，因此执行上述SQL会返回空结果。如果给部门属性历史新增一条记录：

执行SQL后会得到如下结果：

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