如何使用SQL自连接查询工号更新后的员工原始最早入职日期

场景说明 #

需要查询门店员工的真实入职起始日期，当前存在工号更新后入职起始日期被同步修改的问题：员工编号为5041的员工原有工号为A0EH，工号更新后起始日期被同步覆盖，导致在职总时长统计出现偏差。

现有原始表查询结果如下：

期望输出结果如下：

原有代码问题 #

你之前的写法仅做了一层关联，如果存在多次工号变更的场景会失效，且没有正确继承最原始的入职日期。

select
    esd.employee_number,
    (case when esd.old_employee_number is null then es.employee_number else es.old_employee_number end) as old_employee_number,
    esd.entity_id,
    esd.original_start_date
from earliest_start_date as esd
left join earliest_start_date as es
    on (es.employee_number = esd.old_employee_number)

正确实现方案 #

使用递归CTE逐层追溯最原始的工号对应的入职日期，可以兼容任意次数工号变更的场景，适配MySQL 8.0+、PostgreSQL、SQL Server等主流数据库：

WITH RECURSIVE employee_start_cte AS (
    -- 锚点：获取所有无旧工号的最原始入职记录
    SELECT 
        employee_number,
        old_employee_number,
        entity_id,
        original_start_date,
        employee_number AS root_employee_number,
        original_start_date AS real_start_date
    FROM earliest_start_date
    WHERE old_employee_number IS NULL

    UNION ALL

    -- 递归：关联所有工号变更后的记录，继承原始入职信息
    SELECT 
        esd.employee_number,
        esd.old_employee_number,
        esd.entity_id,
        esd.original_start_date,
        esc.root_employee_number,
        esc.real_start_date
    FROM earliest_start_date esd
    INNER JOIN employee_start_cte esc 
        ON esd.old_employee_number = esc.employee_number
)
-- 输出最终结果
SELECT 
    employee_number,
    root_employee_number AS old_employee_number,
    entity_id,
    real_start_date AS original_start_date
FROM employee_start_cte
ORDER BY employee_number;

逻辑说明 #

• 递归锚点先定位所有没有历史工号的原始记录，这部分记录的起始日期就是员工真实的入职时间
• 递归层每一层都将新工号和旧工号关联，直接继承最原始的入职日期，无论中间经历多少次工号变更都能拿到正确的最早入职日期

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