如何编写MySQL查询以计算包含假期的员工工作时段？

需求说明 #

现有vacation表，包含字段：

• idemployee：员工ID
• vacation_from：假期开始日期
• vacation_to：假期结束日期

需查询2023年全年（2023-01-01至2023-12-31）内各员工的工作时段，输出格式需包含work_start（工作时段开始）、work_end（工作时段结束）及说明字段。

正确查询SQL #

WITH RECURSIVE employee_work_periods AS (
    -- 生成2023年初到首个假期前的工作时段，或全年无假期的情况
    SELECT 
        idemployee,
        '2023-01-01' AS work_start,
        CASE 
            WHEN MIN(vacation_from) > '2023-01-01' THEN DATE_SUB(MIN(vacation_from), INTERVAL 1 DAY)
            ELSE '2023-12-31'
        END AS work_end,
        CASE
            WHEN MIN(vacation_from) IS NULL THEN '2023年全年在岗'
            ELSE '2023年初至首个假期前'
        END AS description
    FROM vacation
    WHERE vacation_to >= '2023-01-01' AND vacation_from <= '2023-12-31'
    GROUP BY idemployee
    UNION ALL
    -- 递归生成两个假期之间的工作时段
    SELECT 
        v.idemployee,
        DATE_ADD(v.vacation_to, INTERVAL 1 DAY) AS work_start,
        DATE_SUB(next_vac.vacation_from, INTERVAL 1 DAY) AS work_end,
        CONCAT('假期[', DATE_FORMAT(v.vacation_from, '%Y-%m-%d'), '至', DATE_FORMAT(v.vacation_to, '%Y-%m-%d'), ']结束后至下一个假期前') AS description
    FROM vacation v
    JOIN (
        SELECT 
            idemployee,
            vacation_from,
            LEAD(vacation_from) OVER (PARTITION BY idemployee ORDER BY vacation_from) AS next_vac_start
        FROM vacation
        WHERE vacation_to >= '2023-01-01' AND vacation_from <= '2023-12-31'
    ) next_vac ON v.idemployee = next_vac.idemployee AND v.vacation_from = next_vac.vacation_from
    WHERE next_vac.next_vac_start IS NOT NULL
    UNION ALL
    -- 生成最后一个假期到2023年末的工作时段
    SELECT 
        idemployee,
        DATE_ADD(MAX(vacation_to), INTERVAL 1 DAY) AS work_start,
        '2023-12-31' AS work_end,
        CONCAT('最后一个假期[', DATE_FORMAT(MAX(vacation_from), '%Y-%m-%d'), '至', DATE_FORMAT(MAX(vacation_to), '%Y-%m-%d'), ']结束后至年末') AS description
    FROM vacation
    WHERE vacation_to >= '2023-01-01' AND vacation_from <= '2023-12-31'
    GROUP BY idemployee
    HAVING MAX(vacation_to) < '2023-12-31'
)
-- 过滤无效时段并整理结果
SELECT 
    idemployee,
    work_start,
    work_end,
    description
FROM employee_work_periods
WHERE work_start <= work_end
ORDER BY idemployee, work_start;

关键逻辑说明 #

• 递归CTE覆盖全场景：通过三段逻辑分别处理年初到首个假期、假期间隙、末个假期到年末的工作区间，确保无遗漏。
• 日期边界处理：自动截断跨年度的假期日期，仅统计2023年范围内的时段；通过DATE_ADD/DATE_SUB确保工作时段与假期首尾无缝衔接。
• 场景化说明：针对不同工作区间生成明确的文字描述，便于直观理解时段含义。

常见错误查询问题 #

很多错误查询的核心问题包括：

• 未统计无假期员工的全年工作时段
• 忽略了两个假期之间的间隙工作区间
• 未截断跨年度假期的日期范围，导致统计到2023年以外的时段
• 日期衔接逻辑错误（比如直接用假期结束日作为工作开始日，未加1天）

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