SQL技术问题：如何将日期范围拆解为个人月度月末记录

需求说明 #

为每位员工生成其在职区间（StartDate 至 EndDate）内所有被涵盖的月末日期记录，判定规则为：月末日期需处于员工的在职时间范围内。

现有数据表 #

Records表（员工在职信息） #

Calendar表（日期维度表） #

解决方案一：利用现有Calendar表实现 #

核心思路是先从Calendar表提取唯一月末日期，再关联员工表筛选符合条件的记录：

SELECT DISTINCT
    r.姓名,
    r.StartDate,
    r.EndDate,
    c.EndMonth
FROM
    Records r
INNER JOIN (
    -- 从Calendar表中提取去重后的月末日期
    SELECT DISTINCT EndMonth
    FROM Calendar
) c ON 
    -- 筛选月末日期在员工在职区间内的记录
    c.EndMonth BETWEEN r.StartDate AND r.EndDate
ORDER BY
    c.EndMonth, r.姓名;

逻辑说明 #

1. 子查询对Calendar表的EndMonth去重，避免同一月末生成多条重复记录
2. 通过BETWEEN关联员工表，确保月末日期落在员工的入职与离职日期之间
3. 最终按月末日期、姓名排序，匹配期望输出格式

解决方案二：无需Calendar表，直接生成月末日期 #

如果没有Calendar表，可通过递归CTE生成所需月末日期（以MySQL为例）：

WITH RECURSIVE MonthEnds AS (
    -- 初始化：生成员工最早入职月份的月末日期
    SELECT
        LAST_DAY(MIN(StartDate)) AS EndMonth
    FROM Records
    UNION ALL
    -- 递归生成后续月份的月末日期，直到覆盖最晚离职月份
    SELECT
        LAST_DAY(DATE_ADD(EndMonth, INTERVAL 1 MONTH))
    FROM MonthEnds
    WHERE EndMonth < (SELECT LAST_DAY(MAX(EndDate)) FROM Records)
)
SELECT
    r.姓名,
    r.StartDate,
    r.EndDate,
    me.EndMonth
FROM
    Records r
INNER JOIN MonthEnds me ON
    me.EndMonth BETWEEN r.StartDate AND r.EndDate
ORDER BY
    me.EndMonth, r.姓名;

逻辑说明 #

1. 递归CTE自动生成从员工最早入职到最晚离职期间的所有月末日期
2. 关联员工表筛选出在职区间包含该月末的记录，无需额外去重处理

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