如何为企业补全2019年4月-2020年2月缺失年月的零值记录

嘿，这个问题我之前帮不少人处理过！核心思路就是先构建出你需要的2019年4月到2020年2月的完整月份序列，再和所有企业的列表做交叉关联，最后通过左连接把原始数据拼进来，缺失的Quantity就用0填充。下面给你分几种常用数据库的具体实现代码，按需选用就行：

通用逻辑拆解 #

• 第一步：生成目标时间范围内的所有月份（确保没有遗漏）
• 第二步：提取所有唯一的企业名称
• 第三步：将企业列表和月份列表做交叉连接，得到每家企业对应所有目标月份的基础空数据集
• 第四步：左连接原始业务数据，用COALESCE或ISNULL把空值替换成0

MySQL 实现代码 #

-- 生成目标月份序列（递归CTE）
WITH RECURSIVE months AS (
    SELECT '2019-04-01' AS month_date
    UNION ALL
    SELECT DATE_ADD(month_date, INTERVAL 1 MONTH)
    FROM months
    WHERE month_date < '2020-02-01'
),
-- 获取所有唯一企业
companies AS (
    SELECT DISTINCT Company FROM your_table_name
)
-- 交叉关联+左连接补全数据
SELECT 
    c.Company,
    YEAR(m.month_date) AS year,
    MONTH(m.month_date) AS month,
    COALESCE(t.Quantity, 0) AS Quantity
FROM companies c
CROSS JOIN months m
LEFT JOIN your_table_name t 
    ON c.Company = t.Company
    AND t.year = YEAR(m.month_date)
    AND t.month = MONTH(m.month_date)
ORDER BY c.Company, m.month_date;

PostgreSQL 实现代码 #

-- 生成目标月份序列（generate_series函数）
WITH months AS (
    SELECT generate_series(
        '2019-04-01'::date,
        '2020-02-01'::date,
        '1 month'::interval
    ) AS month_date
),
-- 获取所有唯一企业
companies AS (
    SELECT DISTINCT Company FROM your_table_name
)
-- 交叉关联+左连接补全数据
SELECT 
    c.Company,
    EXTRACT(YEAR FROM m.month_date)::int AS year,
    EXTRACT(MONTH FROM m.month_date)::int AS month,
    COALESCE(t.Quantity, 0) AS Quantity
FROM companies c
CROSS JOIN months m
LEFT JOIN your_table_name t 
    ON c.Company = t.Company
    AND t.year = EXTRACT(YEAR FROM m.month_date)::int
    AND t.month = EXTRACT(MONTH FROM m.month_date)::int
ORDER BY c.Company, m.month_date;

SQL Server 实现代码 #

-- 生成目标月份序列（递归CTE）
WITH months AS (
    SELECT DATEFROMPARTS(2019, 4, 1) AS month_date
    UNION ALL
    SELECT DATEADD(MONTH, 1, month_date)
    FROM months
    WHERE month_date < DATEFROMPARTS(2020, 2, 1)
),
-- 获取所有唯一企业
companies AS (
    SELECT DISTINCT Company FROM your_table_name
)
-- 交叉关联+左连接补全数据
SELECT 
    c.Company,
    YEAR(m.month_date) AS year,
    MONTH(m.month_date) AS month,
    ISNULL(t.Quantity, 0) AS Quantity
FROM companies c
CROSS JOIN months m
LEFT JOIN your_table_name t 
    ON c.Company = t.Company
    AND t.year = YEAR(m.month_date)
    AND t.month = MONTH(m.month_date)
ORDER BY c.Company, m.month_date;

注意事项 #

• 记得把代码里的your_table_name替换成你实际使用的表名
• 原始数据中的year和month如果是数字类型，连接条件已经完全匹配，不需要额外转换
• 最终结果会按企业名称和月份顺序排序，和你期望的目标格式完全一致

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