如何按ID和年份向数据集插入缺失的日历月份？

核心思路 #

先生成每个ID与对应年份的所有12个月份的完整组合，再筛选出原数据中不存在的记录，最后将这些缺失记录插入到原表中。

通用SQL实现（以SQL Server为例） #

-- 生成1-12的月份基础表
WITH months AS (
    SELECT 1 AS month UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
    SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL
    SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL
    SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12
),
-- 提取原表中所有唯一的ID和年份组合
id_year_pairs AS (
    SELECT DISTINCT id, year FROM your_table_name
),
-- 生成所有可能的ID+年份+月份完整组合
full_month_combinations AS (
    SELECT 
        iy.id,
        iy.year,
        -- 转换为两位数字格式的月份，适配原数据格式
        RIGHT('0' + CAST(m.month AS VARCHAR), 2) AS month
    FROM id_year_pairs iy
    CROSS JOIN months m
)
-- 筛选出原表中缺失的记录并插入
INSERT INTO your_table_name (id, year, month)
SELECT fc.id, fc.year, fc.month
FROM full_month_combinations fc
LEFT JOIN your_table_name t
    ON fc.id = t.id 
    AND fc.year = t.year 
    AND fc.month = t.month
WHERE t.id IS NULL;

不同数据库的月份格式适配 #

• MySQL：将月份转换逻辑改为 LPAD(m.month, 2, '0')
• PostgreSQL：将月份转换逻辑改为 TO_CHAR(m.month, 'FM00')
• 如果你的month字段是数值类型（而非字符串），直接使用m.month即可，无需格式转换。

注意事项 #

1. 替换代码中的your_table_name为你的实际表名。
2. 执行前建议先单独运行SELECT部分（去掉INSERT语句），确认要插入的缺失记录是否符合预期。

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