MySQL如何展示无记录月份并将统计空值强制设置为0

核心逻辑 #

需要先构造2021年1-12月的完整月份基准序列，再和订单表的统计结果做左连接，无匹配数据的月份统计值自动补0即可。

最终查询语句 #

SELECT 
    IFNULL(SUM(o.total), 0) AS total_orders,
    MONTHNAME(STR_TO_DATE(m.month_num, '%m')) AS date
FROM (
    -- 生成1-12月的数字基准序列
    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 orders o 
    ON MONTH(o.created_at) = m.month_num
    AND o.is_active = 1
    AND o.tenant_id = 2
    AND YEAR(o.created_at) = 2021
GROUP BY m.month_num
ORDER BY m.month_num ASC

注意事项 #

• 所有订单表的过滤条件必须放在LEFT JOIN的ON子句中，不能放到全局WHERE位置，否则会过滤掉无订单的月份
• 用IFNULL函数将无匹配数据月份的统计值从NULL转为0，符合需求
• 替换原有的created_at like '%2021%'为YEAR(o.created_at) = 2021，执行效率更高，同时避免匹配到包含2021字符串的非日期脏数据
• 如果created_at字段有索引，可将YEAR(o.created_at) = 2021替换为o.created_at BETWEEN '2021-01-01 00:00:00' AND '2021-12-31 23:59:59'，可命中索引提升查询速度
• 最终按月份数字排序，保证输出顺序为1月到12月，符合常规展示需求

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