如何在不使用LAG/LEAD函数的情况下计算当前岗位任职时长？

需求说明 #

给定员工当前岗位编码（如DA123-2）和历史岗位编码（如DA123-1），需忽略岗位编码中连字符-后的后缀内容，匹配前缀一致的所有岗位记录，并以最早的岗位变更日期作为当前岗位的任职起始日期。

示例场景：员工岗位变更轨迹为 ITW124 -> DA123 -> DA123-1 -> DA123-2，后三个岗位前缀均为DA123，因此任职起始日期取DA123对应的变更日期（2024年3月23日）。

限制条件 #

系统不支持LAG()/LEAD()等分析函数，常规的Job_ID关联历史日期方法无效。

解决方案 #

通过字符串截取提取前缀+自关联/子查询实现，以下是通用SQL逻辑（适配多数关系型数据库，需根据实际数据库调整字符串函数）：

1. 单员工单岗位查询

若需查询指定员工当前岗位的起始日期，先提取当前岗位前缀，再筛选该员工所有前缀匹配的历史记录，取最小变更日期：

SELECT 
    emp_id,
    MIN(change_date) AS current_job_start_date
FROM employee_job_history
WHERE 
    emp_id = '目标员工ID'
    AND (
        -- 提取岗位编码前缀（处理无连字符的情况）
        CASE 
            WHEN INSTR(job_id, '-') > 0 THEN SUBSTR(job_id, 1, INSTR(job_id, '-') - 1)
            ELSE job_id 
        END
    ) = (
        -- 提取当前岗位的前缀（示例当前岗位为DA123-2）
        CASE 
            WHEN INSTR('DA123-2', '-') > 0 THEN SUBSTR('DA123-2', 1, INSTR('DA123-2', '-') - 1)
            ELSE 'DA123-2' 
        END
    )
GROUP BY emp_id;

2. 批量处理所有员工当前岗位

若需批量计算所有员工的当前岗位起始日期，先通过子查询定位每个员工的最新岗位，再关联历史记录筛选前缀匹配的最早日期：

SELECT 
    lj.emp_id,
    lj.job_id AS current_job_id,
    MIN(ejh.change_date) AS current_job_start_date
FROM (
    -- 子查询获取每个员工的最新岗位记录
    SELECT emp_id, job_id, change_date
    FROM employee_job_history ejh
    WHERE change_date = (
        SELECT MAX(change_date)
        FROM employee_job_history
        WHERE emp_id = ejh.emp_id
    )
) lj
JOIN employee_job_history ejh ON lj.emp_id = ejh.emp_id
WHERE 
    (
        CASE 
            WHEN INSTR(ejh.job_id, '-') > 0 THEN SUBSTR(ejh.job_id, 1, INSTR(ejh.job_id, '-') - 1)
            ELSE ejh.job_id 
        END
    ) = (
        CASE 
            WHEN INSTR(lj.job_id, '-') > 0 THEN SUBSTR(lj.job_id, 1, INSTR(lj.job_id, '-') - 1)
            ELSE lj.job_id 
        END
    )
GROUP BY lj.emp_id, lj.job_id;

字符串函数适配说明 #

不同数据库的字符串截取函数略有差异，可根据实际环境替换：

• MySQL：用SUBSTRING_INDEX(job_id, '-', 1)替代上述CASE逻辑，自动提取连字符前的前缀
• SQL Server：用LEFT(job_id, CHARINDEX('-', job_id + '-') - 1)提取前缀
• Oracle：原CASE逻辑可直接使用，或简化为REGEXP_SUBSTR(job_id, '^[^-]+')（正则提取前缀）

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