MySQL按placed_date分组如何返回无订单的对应日期记录

原有查询存在的问题 #

• 表名引用错误：实际表名为orders，原有SQL误写为SQL关键字order，会触发语法错误
• 分组规则缺陷：仅按日期的天维度分组，会合并不同月份的同天数据，统计结果不准确
• 缺少日期基准：仅从订单表查询天然会过滤掉没有订单的日期，无法得到全量日期结果

实现SQL（无需创建新表、兼容旧版本MySQL） #

SELECT 
    IFNULL(COUNT(o.id), 0) AS total_orders,
    d.day_of_month,
    d.month_of_year
FROM (
    -- 生成2014年所有合法日期的基准表
    SELECT 
        months.m AS month_of_year,
        days.d AS day_of_month,
        STR_TO_DATE(CONCAT('2014-', months.m, '-', days.d), '%Y-%m-%d') AS full_date
    FROM
        -- 生成1-12月的序列，可按需调整范围
        (SELECT 1 AS m UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 
         UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12) AS months
    CROSS JOIN
        -- 生成1-31日的序列
        (SELECT 1 AS d UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10
         UNION SELECT 11 UNION SELECT 12 UNION SELECT 13 UNION SELECT 14 UNION SELECT 15 UNION SELECT 16 UNION SELECT 17 UNION SELECT 18 UNION SELECT 19 UNION SELECT 20
         UNION SELECT 21 UNION SELECT 22 UNION SELECT 23 UNION SELECT 24 UNION SELECT 25 UNION SELECT 26 UNION SELECT 27 UNION SELECT 28 UNION SELECT 29 UNION SELECT 30
         UNION SELECT 31) AS days
    -- 过滤非法日期（如2月30日、4月31日等），如需限定查询月份可在此加条件，例：AND months.m BETWEEN 1 AND 5
    HAVING full_date IS NOT NULL
) AS d
LEFT JOIN orders o ON DATE(o.placed_date) = d.full_date
GROUP BY d.month_of_year, d.day_of_month
ORDER BY d.month_of_year ASC, d.day_of_month ASC;

逻辑说明 #

1. 内层通过UNION语句内联生成月份和日期的数字序列，交叉连接得到所有可能的月日组合，不需要创建额外的物理表
2. 用STR_TO_DATE构造完整日期自动过滤掉不存在的非法日期，得到全量合法日期基准
3. 左关联订单表，无订单的日期计数结果为NULL，通过IFNULL转换为0
4. 按月份、日期排序后输出，符合预期的结果格式

内容的提问来源于stack exchange，提问作者丹尺爪工れ