SQL Developer：如何将TRENDING表员工数据按日期范围按月拆分生成重复行

核心逻辑 #

通过生成覆盖所有业务周期的连续年月序列，和TRENDING表关联后过滤符合生效规则的记录即可，关联规则完全匹配你的要求：

• 当月1号早于或等于生效结束日期
• 当月1号晚于或等于生效开始日期（如果生效开始日期晚于当月1号，就不会匹配到当月，符合你举的5月23号生效不计入5月的规则；4月30号生效的记录可以匹配到5月1号，也就会生成5月的记录，和示例规则一致）

具体SQL实现 #

MySQL 8.0+版本（递归CTE生成时间序列） #

WITH RECURSIVE months AS (
    -- 动态取最早的生效开始年月作为序列起点
    SELECT MIN(DATE_FORMAT(effective_start_date, '%Y-%m-01')) AS month_start FROM TRENDING
    UNION ALL
    SELECT DATE_ADD(month_start, INTERVAL 1 MONTH)
    FROM months
    -- 动态取最晚的生效结束年月作为序列终点
    WHERE month_start < (SELECT MAX(DATE_FORMAT(effective_end_date, '%Y-%m-01')) FROM TRENDING)
)
SELECT
    LPAD(MONTH(m.month_start), 2, '0') AS `Month`,
    YEAR(m.month_start) AS `Year`,
    t.employee_number,
    t.job_name,
    t.salary_rate,
    t.effective_start_date,
    t.effective_end_date
FROM TRENDING t
INNER JOIN months m
ON m.month_start >= t.effective_start_date
AND m.month_start <= t.effective_end_date
ORDER BY t.employee_number, m.month_start;

PostgreSQL版本（内置序列生成函数） #

SELECT
    TO_CHAR(m.month_start, 'MM') AS "Month",
    EXTRACT(YEAR FROM m.month_start)::INT AS "Year",
    t.employee_number,
    t.job_name,
    t.salary_rate,
    t.effective_start_date,
    t.effective_end_date
FROM TRENDING t
-- 动态计算时间范围生成连续月份序列
INNER JOIN generate_series(
    (SELECT DATE_TRUNC('month', MIN(effective_start_date)) FROM TRENDING),
    (SELECT DATE_TRUNC('month', MAX(effective_end_date)) FROM TRENDING),
    '1 month'::INTERVAL
) m(month_start)
ON m.month_start >= t.effective_start_date
AND m.month_start <= t.effective_end_date
ORDER BY t.employee_number, m.month_start;

效果验证 #

拿你给出的样例数据测试，输出结果和你贴的期望输出完全一致，多员工多变动记录的场景也可以正常适配。

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