如何根据NoOfMonths字段值生成对应条数的月度记录SQL查询

通用递归方案（支持绝大多数主流数据库） #

递归CTE是兼容性最高的实现方式，支持MySQL 8.0+、SQL Server、PostgreSQL、Oracle 11gR2+等所有支持递归语法的数据库，以下是不同数据库的适配写法：

MySQL 8.0+ #

WITH RECURSIVE month_series AS (
    -- 取每条记录的起始月份作为第一条数据
    SELECT 
        Id,
        StartDate AS Date,
        1 AS month_count,
        NoOfMonths
    FROM your_table_name
    UNION ALL
    -- 逐月累加生成后续月份，直到达到指定月数
    SELECT 
        Id,
        DATE_ADD(Date, INTERVAL 1 MONTH) AS Date,
        month_count + 1,
        NoOfMonths
    FROM month_series
    WHERE month_count < NoOfMonths
)
SELECT Id, Date 
FROM month_series
ORDER BY Id, Date;

SQL Server #

WITH month_series AS (
    SELECT 
        Id,
        StartDate AS Date,
        1 AS month_count,
        NoOfMonths
    FROM your_table_name
    UNION ALL
    SELECT 
        Id,
        DATEADD(MONTH, 1, Date) AS Date,
        month_count + 1,
        NoOfMonths
    FROM month_series
    WHERE month_count < NoOfMonths
)
SELECT Id, Date 
FROM month_series
ORDER BY Id, Date
-- 取消默认100层递归限制，适配大月数场景
OPTION (MAXRECURSION 0);

PostgreSQL 简化写法 #

PostgreSQL可以用内置的generate_series函数实现更简洁的写法，性能更优：

SELECT 
    t.Id,
    (t.StartDate + (s.n - 1) * INTERVAL '1 month')::DATE AS Date
FROM your_table_name t
JOIN generate_series(1, (SELECT MAX(NoOfMonths) FROM your_table_name)) s(n)
    ON s.n <= t.NoOfMonths
ORDER BY t.Id, Date;

注意事项 #

• 所有代码中的your_table_name请替换为你实际的表名
• 如果单条记录的NoOfMonths数值超过1000，建议提前调整数据库的递归层数配置避免报错
• 上述代码返回结果和需求中的预期输出完全一致，Id为1返回2条逐月记录，Id为2返回3条逐月记录

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