如何修改MySQL查询语句显示2019年所有月份的商品数量总和（含零值）

原语句只返回有数据的月份，是因为ip_invoice_items表里没有对应月份的记录，ISNULL函数没法处理根本没被查询出来的行，自然没效果。要显示所有12个月份，得先构造出2019年的完整月份列表，再和数据表做左连接，这样就能把所有月份都保留下来，没数据的月份总和显示为0。

修改后的SQL语句（MySQL 8.0+适用） #

WITH months AS (
    SELECT 1 AS month_num 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
)
SELECT 
    DATE_FORMAT(CONCAT('2019-', m.month_num, '-01'), '%m.%Y') AS date,
    IFNULL(SUM(ii.item_quantity), 0) AS sum
FROM 
    months m
LEFT JOIN 
    ip_invoice_items ii ON MONTH(ii.item_date_added) = m.month_num 
                        AND YEAR(ii.item_date_added) = 2019
GROUP BY 
    m.month_num
ORDER BY 
    m.month_num;

代码说明 #

• 用WITH子句生成1到12的月份数字列表，搭建2019年的完整月份框架。
• 通过LEFT JOIN关联月份列表和数据表，确保无数据的月份也能保留行记录。
• IFNULL(SUM(ii.item_quantity), 0)把无数据月份的总和从NULL转为0，符合统计需求。
• 按生成的月份数字分组、排序，保证结果按1-12月顺序输出。

兼容低版本MySQL（低于8.0）的写法 #

SELECT 
    DATE_FORMAT(CONCAT('2019-', m.month_num, '-01'), '%m.%Y') AS date,
    IFNULL(SUM(ii.item_quantity), 0) AS sum
FROM 
    (SELECT 1 AS month_num 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 
    ip_invoice_items ii ON MONTH(ii.item_date_added) = m.month_num 
                        AND YEAR(ii.item_date_added) = 2019
GROUP BY 
    m.month_num
ORDER BY 
    m.month_num;

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