如何用SQL将员工雇佣记录转换为月度在职员工列表？

原始员工表 #

你说得对，GROUP BY确实不适用这个场景——我们需要把每个员工在职的每个月拆成独立行，核心是先构建一个覆盖所有目标月份的序列，再和员工表关联筛选符合条件的记录。以下是主流SQL环境的具体实现：

1. MySQL 8.0+ 写法 #

利用递归CTE生成月份序列，再关联员工表筛选：

WITH RECURSIVE month_sequence AS (
    -- 取最早入职日期的当月第一天作为起始月
    SELECT 
        DATE_FORMAT(MIN(STR_TO_DATE(EmploymentStarted, '%Y%m%d')), '%Y-%m-01') AS month_start
    FROM employees
    UNION ALL
    -- 递归生成后续月份，直到覆盖最晚离职日期的当月
    SELECT 
        DATE_ADD(month_start, INTERVAL 1 MONTH)
    FROM month_sequence
    WHERE month_start <= (SELECT DATE_FORMAT(MAX(STR_TO_DATE(EmploymentEnded, '%Y%m%d')), '%Y-%m-01') FROM employees)
)
SELECT
    MONTHNAME(ms.month_start) AS Month,
    YEAR(ms.month_start) AS Year,
    e.Employee
FROM month_sequence ms
CROSS JOIN employees e
WHERE
    -- 判断员工当月在职：入职日期不晚于当月最后一天，离职日期不早于当月第一天
    STR_TO_DATE(e.EmploymentStarted, '%Y%m%d') <= LAST_DAY(ms.month_start)
    AND STR_TO_DATE(e.EmploymentEnded, '%Y%m%d') >= ms.month_start
ORDER BY
    ms.month_start, e.Employee;

2. PostgreSQL 写法 #

用generate_series快速生成月份序列，逻辑更简洁：

WITH month_sequence AS (
    SELECT
        generate_series(
            DATE_TRUNC('month', MIN(TO_DATE(EmploymentStarted, 'YYYYMMDD'))),
            DATE_TRUNC('month', MAX(TO_DATE(EmploymentEnded, 'YYYYMMDD'))),
            INTERVAL '1 month'
        ) AS month_start
    FROM employees
)
SELECT
    TO_CHAR(ms.month_start, 'FMMonth') AS Month,
    EXTRACT(YEAR FROM ms.month_start) AS Year,
    e.Employee
FROM month_sequence ms
CROSS JOIN employees e
WHERE
    TO_DATE(e.EmploymentStarted, 'YYYYMMDD') <= (ms.month_start + INTERVAL '1 month - 1 day')
    AND TO_DATE(e.EmploymentEnded, 'YYYYMMDD') >= ms.month_start
ORDER BY
    ms.month_start, e.Employee;

核心逻辑说明 #

1. 生成月份序列：先确定需要覆盖的时间范围（从最早入职月到最晚离职月），生成每个月的第一天作为标识；
2. 交叉筛选：将每个月份与所有员工做交叉连接，通过日期判断筛选出在职员工——只要员工入职日期不晚于当月最后一天、离职日期不早于当月第一天，就视为当月在职；
3. 格式化输出：用内置函数将日期转换为英文月份名称和年份，最后按月份、员工排序。

内容的提问来源于stack exchange，提问作者Dimitrios Torssøn