MySQL 查詢本周數據按天分組並補齊無數據日期為 0 的完整方案

問題現場

現有 SQL 按天分組統計，但數據庫中缺失某天記錄導致結果集缺少該行。需求：本周 (周一至周日) 7 天全部顯示，無數據日各指標顯示 0。

核心思路：日歷表/衍生表右連接

MySQL 8.0+ 支持 CTE (Common Table Expression) 生成虛擬日歷表，舊版本可用系統表 mysql.help_topic 或聯合查詢生成序列。

MySQL 8.0+ 標準寫法 (CTE + 生成序列)

WITH RECURSIVE dates AS (
    -- 獲取本周周一日期
    SELECT DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY) AS dt
    UNION ALL
    SELECT DATE_ADD(dt, INTERVAL 1 DAY) FROM dates WHERE dt < DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY) + INTERVAL 6 DAY
),
stats AS (
    SELECT DATE(uploadTime_beg) AS dt,
           SUM(field01) AS sumStatus1, SUM(field02) AS sumStatus2,
           SUM(field03) AS sumStatus3, SUM(field04) AS sumStatus4,
           SUM(field05) AS sumStatus5
    FROM health_realdata
    WHERE YEARWEEK(uploadTime_beg, 1) = YEARWEEK(CURDATE(), 1) -- 模式1: 周一為每周第一天
    GROUP BY DATE(uploadTime_beg)
)
SELECT d.dt AS `date`,
       COALESCE(s.sumStatus1, 0) AS sumStatus1,
       COALESCE(s.sumStatus2, 0) AS sumStatus2,
       COALESCE(s.sumStatus3, 0) AS sumStatus3,
       COALESCE(s.sumStatus4, 0) AS sumStatus4,
       COALESCE(s.sumStatus5, 0) AS sumStatus5
FROM dates d
LEFT JOIN stats s ON d.dt = s.dt
ORDER BY d.dt;

MySQL 5.7 兼容寫法 (系統表生成序列)

SELECT d.dt AS `date`,
       COALESCE(s.sumStatus1, 0) AS sumStatus1,
       COALESCE(s.sumStatus2, 0) AS sumStatus2,
       COALESCE(s.sumStatus3, 0) AS sumStatus3,
       COALESCE(s.sumStatus4, 0) AS sumStatus4,
       COALESCE(s.sumStatus5, 0) AS sumStatus5
FROM (
    SELECT DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY) + INTERVAL n DAY AS dt
    FROM (
        SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
        UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
    ) nums
) d
LEFT JOIN (
    SELECT DATE(uploadTime_beg) AS dt,
           SUM(field01) AS sumStatus1, SUM(field02) AS sumStatus2,
           SUM(field03) AS sumStatus3, SUM(field04) AS sumStatus4,
           SUM(field05) AS sumStatus5
    FROM health_realdata
    WHERE YEARWEEK(uploadTime_beg, 1) = YEARWEEK(CURDATE(), 1)
    GROUP BY DATE(uploadTime_beg)
) s ON d.dt = s.dt
ORDER BY d.dt;

關鍵點解析

1. 周界定義一致性： 統計與日歷生成均使用 WEEKDAY() (周一=0) 和 YEARWEEK(..., 1) (模式1：周一起始)，確保「本周」定義完全一致。
2. LEFT JOIN 保左表： 日歷表為左表，保證 7 行全部輸出。
3. COALESCE 處理 NULL： 關鍵指標字段 NULL -> 0。
4. 索引建議： uploadTime_beg 上建立索引，WHERE YEARWEEK(...) 可用索引下推 (MySQL 5.6+)。

性能優化提示

• 若頻繁查詢，建議創建物化視圖/汇總表，每日定時任務刷新。
• 大數據量下，YEARWEEK 函數可能導致全表掃描，改用 uploadTime_beg >= '2024-01-01' AND uploadTime_beg < '2024-01-08' 範圍查詢利用索引。

「虛擬日歷表 + 左連接 + COALESCE」是補齊時間序列缺失值的標準 SQL 模式，掌握後可輕鬆應用於日報、周報、月報各類報表需求。