MySQL 查詢每月記錄總數：缺月補零的標準寫法（衍生表/遞歸 CTE）

核心思路

構造 1~12 月份基準表，LEFT JOIN 業務表聚合結果，COALESCE/IFNULL 補零。

方案一：臨時衍生表 (兼容所有版本)

SELECT m.month_num AS 月份, IFNULL(t.cnt, 0) AS 數量
FROM (
    SELECT 1 AS month_num 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
) m
LEFT JOIN (
    SELECT MONTH(sale_date) AS m, COUNT(*) AS cnt
    FROM sales
    WHERE YEAR(sale_date) = 2007
    GROUP BY MONTH(sale_date)
) t ON m.month_num = t.m
ORDER BY m.month_num;

方案二：遞歸 CTE (MySQL 8.0+)

WITH RECURSIVE months AS (
    SELECT 1 AS month_num
    UNION ALL
    SELECT month_num + 1 FROM months WHERE month_num < 12
)
SELECT m.month_num AS 月份, COALESCE(t.cnt, 0) AS 數量
FROM months m
LEFT JOIN (
    SELECT MONTH(sale_date) AS m, COUNT(*) AS cnt
    FROM sales
    WHERE YEAR(sale_date) = 2007
    GROUP BY MONTH(sale_date)
) t ON m.month_num = t.m
ORDER BY m.month_num;

方案三：永久輔助表 (高頻查詢推薦)

CREATE TABLE `months` (`month_num` TINYINT UNSIGNED PRIMARY KEY);
INSERT INTO `months` VALUES (1),(2),...,(12);

-- 之後直接 JOIN
SELECT m.month_num, COALESCE(t.cnt,0)
FROM `months` m
LEFT JOIN (...) t ON m.month_num = t.m
ORDER BY m.month_num;

索引建議

ALTER TABLE sales ADD INDEX idx_sale_date (sale_date);
-- 或覆蓋索引
ALTER TABLE sales ADD INDEX idx_ym (YEAR(sale_date), MONTH(sale_date));