SQL如何为每个用户补全全月份记录 缺失月份对应值填充为0

核心实现思路 #

• 首先生成1-12月的完整月份序列
• 提取原表中所有去重的用户ID，和月份序列做笛卡尔积，得到「每个用户对应12个月份」的全量基准组合
• 用基准表左关联原业务表，将匹配不到的Value字段默认替换为0即可

通用实现代码（支持所有支持CTE的数据库，如MySQL8.0+、PostgreSQL、SQL Server等） #

WITH RECURSIVE months AS (
    SELECT 1 AS month
    UNION ALL
    SELECT month + 1 FROM months WHERE month < 12
),
user_list AS (
    SELECT DISTINCT UserId FROM your_table_name -- 替换为你的实际表名
)
SELECT 
    u.UserId,
    m.month AS Month,
    COALESCE(t.Value, 0) AS Value
FROM user_list u
CROSS JOIN months m
LEFT JOIN your_table_name t 
    ON u.UserId = t.UserId AND m.month = t.Month
ORDER BY u.UserId, m.month;

低版本MySQL（不支持CTE）兼容写法 #

如果是不支持CTE的低版本MySQL，可以用UNION ALL手动生成月份序列：

SELECT 
    u.UserId,
    m.month AS Month,
    IFNULL(t.Value, 0) AS Value
FROM (
    SELECT DISTINCT UserId FROM your_table_name
) u
CROSS JOIN (
    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
) m
LEFT JOIN your_table_name t 
    ON u.UserId = t.UserId AND m.month = t.Month
ORDER BY u.UserId, m.month;

注意事项 #

• 把代码里的your_table_name替换为你实际的表名即可
• COALESCE是SQL标准函数，兼容所有主流数据库，作用是返回第一个非空值，匹配不到原表记录时就返回0
• 最终输出会默认按用户ID、月份升序排序，和要求的输出格式完全一致

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