如何将positions表中日期区间内的记录扩展为月度行？

要将positions表的每一行扩展为起始日期到结束日期之间的月度记录，不同数据库有不同的实现方式，以下是主流数据库的解决方案：

PostgreSQL 方案 #

PostgreSQL自带generate_series函数，可直接生成日期序列，配合交叉连接快速实现扩展：

SELECT 
    p.id,
    p.role,
    TO_CHAR(month_date, 'YYYY-MM') AS month
FROM positions p
CROSS JOIN generate_series(
    TO_DATE(p.startdate, 'YYYY-MM'),
    TO_DATE(p.enddate, 'YYYY-MM'),
    INTERVAL '1 month'
) AS month_date
ORDER BY p.id, month;

• 先将startdate和enddate转换为日期类型，用generate_series生成两个日期之间的月度序列
• 通过CROSS JOIN将原表每条记录与对应月度序列关联，最终格式化日期为YYYY-MM格式输出

MySQL 方案 #

MySQL 8.0及以上版本支持递归CTE，可通过递归生成月度序列：

WITH RECURSIVE month_series AS (
    SELECT 
        id,
        role,
        STR_TO_DATE(startdate, '%Y-%m') AS current_month,
        STR_TO_DATE(enddate, '%Y-%m') AS end_month
    FROM positions
    UNION ALL
    SELECT 
        id,
        role,
        DATE_ADD(current_month, INTERVAL 1 MONTH),
        end_month
    FROM month_series
    WHERE current_month < end_month
)
SELECT 
    id,
    role,
    DATE_FORMAT(current_month, '%Y-%m') AS month
FROM month_series
ORDER BY id, month;

• 递归CTE的初始部分读取原表数据并转换日期格式
• 递归部分逐月生成新的日期，直到达到end_month
• 最后将日期格式化为YYYY-MM输出

SQL Server 方案 #

SQL Server可通过递归CTE实现月度序列生成：

WITH month_series AS (
    SELECT 
        id,
        role,
        DATEFROMPARTS(LEFT(startdate,4), RIGHT(startdate,2), 1) AS current_month,
        DATEFROMPARTS(LEFT(enddate,4), RIGHT(enddate,2), 1) AS end_month
    FROM positions
    UNION ALL
    SELECT 
        id,
        role,
        DATEADD(MONTH, 1, current_month),
        end_month
    FROM month_series
    WHERE current_month < end_month
)
SELECT 
    id,
    role,
    FORMAT(current_month, 'yyyy-MM') AS month
FROM month_series
ORDER BY id, month;

• 先将YYYY-MM格式的字符串拆解为年、月，用DATEFROMPARTS转换为日期类型
• 递归生成下一个月的日期，直到等于end_month
• 最终格式化日期为YYYY-MM格式输出

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