PostgreSQL：基于单条记录动态生成多行数据

我有一个名为freq_mnth（月度频率）的字段，处理规则如下：

• 当freq_mnth值为3时，对应记录生成4条，每条日期依次递增3个月（覆盖全年）
• 当freq_mnth值为4时，生成3条记录，日期依次递增4个月
• 当freq_mnth值为12时，保留原记录即可

现有数据表：

期望结果：

注：原示例中部分日期不符合"依次递增对应月份"的规则，以上为按规则生成的正确结果

可以通过生成数字辅助表+关联查询的方式实现，该方法简洁高效，适配多数主流数据库。

核心思路 #

1. 生成包含0-3的数字表（覆盖最多4条拆分记录的需求）
2. 原表与数字表关联，筛选满足数字 < 12/freq_mnth的组合（12除以频率即为全年需生成的记录数）
3. 对每条原记录，按数字 × freq_mnth的月份数递增日期

SQL代码示例（SQL Server） #

WITH nums AS (
    SELECT 0 AS n 
    UNION ALL SELECT 1 
    UNION ALL SELECT 2 
    UNION ALL SELECT 3
)
SELECT 
    t.Id,
    DATEADD(month, n * t.freq_mnth, t.Date) AS Date,
    t.freq_mnth
FROM your_table t
JOIN nums n 
    ON n.n < 12 / t.freq_mnth
ORDER BY t.Id, Date;

适配其他数据库的写法 #

• MySQL：替换DATEADD为DATE_ADD

WITH nums AS (
    SELECT 0 AS n 
    UNION ALL SELECT 1 
    UNION ALL SELECT 2 
    UNION ALL SELECT 3
)
SELECT 
    t.Id,
    DATE_ADD(t.Date, INTERVAL n * t.freq_mnth MONTH) AS Date,
    t.freq_mnth
FROM your_table t
JOIN nums n 
    ON n.n < 12 / t.freq_mnth
ORDER BY t.Id, Date;

• PostgreSQL：使用INTERVAL语法

WITH nums AS (
    SELECT 0 AS n 
    UNION ALL SELECT 1 
    UNION ALL SELECT 2 
    UNION ALL SELECT 3
)
SELECT 
    t.Id,
    t.Date + (n * t.freq_mnth || ' months')::INTERVAL AS Date,
    t.freq_mnth
FROM your_table t
JOIN nums n 
    ON n.n < 12 / t.freq_mnth
ORDER BY t.Id, Date;

逻辑说明 #

• 数字表nums提供0-3的整数，对应拆分记录的索引（从0开始）
• 12 / t.freq_mnth计算每条原记录的生成条数：3→4条、4→3条、12→1条
• 通过n * t.freq_mnth计算需递增的月份数，生成对应日期

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