Oracle SQL查询：计算多次离职再入职员工的服务时长

需求说明 #

按员工编号（EMPNO）计算累计服务时长，规则如下：

• 分段计算：首次入职日期（HIREDATE）到对应离职日期（TERMDATE），后续复职日期（REHIREDATE）到对应离职日期，依此类推
• 在职员工的最后一段服务时长，用当前系统日期（SYSDATE）替代TERMDATE计算
• 结果以「X年Y个月」的格式输出

示例数据 #

解决方案SQL #

WITH emp_service_periods AS (
    -- 提取第一段服务：首次入职到第一次离职
    SELECT 
        empno,
        hiredate AS start_date,
        termdate AS end_date
    FROM emp_service
    WHERE ROW_NUMBER() OVER (PARTITION BY empno ORDER BY termdate) = 1
    UNION ALL
    -- 提取中间段服务：复职到对应离职
    SELECT 
        empno,
        rehiredate AS start_date,
        termdate AS end_date
    FROM emp_service
    WHERE ROW_NUMBER() OVER (PARTITION BY empno ORDER BY termdate) > 1
    UNION ALL
    -- 提取最后一段在职服务：最后一次复职到当前日期
    SELECT 
        empno,
        rehiredate AS start_date,
        SYSDATE AS end_date
    FROM emp_service
    WHERE ROW_NUMBER() OVER (PARTITION BY empno ORDER BY termdate DESC) = 1
),
total_service_calc AS (
    -- 汇总每个员工的总服务月份
    SELECT 
        empno,
        SUM(MONTHS_BETWEEN(end_date, start_date)) AS total_months
    FROM emp_service_periods
    GROUP BY empno
)
-- 转换为年+月的格式化输出
SELECT 
    empno,
    FLOOR(total_months / 12) || '年' || MOD(FLOOR(total_months), 12) || '个月' AS total_service_duration
FROM total_service_calc;

逻辑说明 #

1. emp_service_periods 公共表表达式：拆分出员工的所有服务时间段，覆盖首次入职、中间复职、最后在职三个阶段
2. total_service_calc 公共表表达式：用MONTHS_BETWEEN函数计算每个时间段的月份差，汇总得到员工的总服务月份
3. 最终查询：将总月份转换为「年+月」的格式，FLOOR(total_months/12)取整得到年数，MOD(FLOOR(total_months),12)得到剩余月份数

注意事项 #

• 确保HIREDATE、TERMDATE、REHIREDATE列是Oracle DATE类型，避免字符串转换错误
• 若需更精确的月份统计（如四舍五入），可将MONTHS_BETWEEN的结果用ROUND()函数处理
• 若员工存在更多次复职记录，该SQL会自动识别并计算所有分段时长

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