SQL实现员工岗位任职时长查询及无变动场景函数选用

依赖的核心函数 #

• 窗口排序函数：ROW_NUMBER() OVER()，按员工维度对任职记录按时间倒序排位，快速定位当前岗位、上一岗位记录
• 日期间隔计算函数：根据使用的数据库选型对应函数，MySQL用TIMESTAMPDIFF、Oracle用MONTHS_BETWEEN、SQL Server用DATEDIFF，用来计算两段日期之间的整月间隔，作为年、月换算的基础
• 空值置换函数：COALESCE（所有主流数据库通用），用来处理未换岗员工的上一岗位时长取值、在岗中员工的截止日期取值
• 当前日期获取函数：CURRENT_DATE（MySQL/PG）、SYSDATE（Oracle）、GETDATE()（SQL Server），用来计算仍在任的当前岗位的实际时长

实现逻辑 #

1. 先对每个员工（以ASG_NUMBER为唯一标识维度）的所有任职记录，按START_DATE从晚到早排序，最新的在任记录（END_DATE为空/为预设的远期值）排位为1，即当前岗位；排位为2的即上一岗位
2. 单段任职时长计算规则：如果记录的END_DATE有值，计算START_DATE到END_DATE的整月间隔；如果END_DATE为空，计算START_DATE到当前系统日期的整月间隔
3. 结果映射规则：
   • 排位1的记录时长直接作为当前岗位任职时长
   • 如果员工存在排位2的记录，取该记录时长作为上一岗位任职时长；如果不存在（即员工从未发生岗位变动），直接复用当前岗位的时长值，保证两个字段取值一致
   • 最终将总间隔月数拆分为「年数=总月数整除12，月数=总月数模12」，拼接为X年Y月的展示格式

参考SQL（以MySQL 8.0+版本为例） #

-- 先给每个员工的任职记录按时间倒序打排位标记
WITH ranked_job AS (
    SELECT
        ASG_NUMBER,
        START_DATE,
        END_DATE,
        ROW_NUMBER() OVER (
            PARTITION BY ASG_NUMBER 
            ORDER BY START_DATE DESC, END_DATE DESC
        ) AS job_rank
    FROM emp_asg_job_detail -- 替换为实际业务表名即可
),
-- 计算每段任职的总月数
duration_calc AS (
    SELECT
        ASG_NUMBER,
        job_rank,
        TIMESTAMPDIFF(
            MONTH,
            START_DATE,
            COALESCE(END_DATE, CURRENT_DATE)
        ) AS total_month
    FROM ranked_job
)
-- 关联输出最终结果
SELECT
    curr.ASG_NUMBER,
    CONCAT(FLOOR(curr.total_month / 12), '年', curr.total_month % 12, '月') 
        AS `Time in current_Position`,
    CONCAT(
        FLOOR(COALESCE(prev.total_month, curr.total_month) / 12),
        '年',
        COALESCE(prev.total_month, curr.total_month) % 12,
        '月'
    ) AS `Time in Previous Position`
FROM duration_calc curr
LEFT JOIN duration_calc prev
    ON curr.ASG_NUMBER = prev.ASG_NUMBER
    AND prev.job_rank = 2
WHERE curr.job_rank = 1;

注意点 #

• 如果数据库不支持窗口函数（比如MySQL5.x及以下版本），可以用子查询关联count的方式实现相同的排序打标逻辑
• 如果存在同一天生效的多条岗位变更记录，需要在排序逻辑里补充业务优先级字段（比如更新时间、岗位层级）避免排序错乱
• 时长计算默认按整月统计，业务如果需要精确到天，可以在月数计算后补充零头天数的逻辑

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