MySQL 查詢本週數據並按天分組：補齊無數據日期顯示 0

原始 SQL

SELECT DATE_FORMAT(uploadTime_beg, '%Y-%m-%d') AS time,
       SUM(field01) AS sumStatus1, ..., SUM(field05) AS sumStatus5
FROM health_realdata
WHERE DATE_FORMAT(uploadTime_beg, '%Y%u') = DATE_FORMAT(CURDATE(), '%Y%u')
GROUP BY DATE_FORMAT(uploadTime_beg, '%Y-%m-%d');

問題：無資料的日期不會出現在結果中。

解決方案：日曆輔助表 + RIGHT JOIN

1. 建立日曆表 (永久表或 CTE)

CREATE TABLE calendar_dt (
    dt DATE PRIMARY KEY
);
-- 填入 2000-01-01 到 2099-12-31
INSERT INTO calendar_dt SELECT DATE_ADD('2000-01-01', INTERVAL seq DAY)
FROM (SELECT @row := @row + 1 AS seq FROM 
      (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2) t1,
      (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2) t2,
      (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2) t3,
      (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2) t4,
      (SELECT @row := -1) r) d
WHERE DATE_ADD('2000-01-01', INTERVAL seq DAY) <= '2099-12-31';

2. 查詢本週範圍

SET @week_start = DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY); -- 本週一
SET @week_end   = DATE_ADD(@week_start, INTERVAL 6 DAY);                -- 本週日

3. 最終查詢

SELECT c.dt AS time,
       COALESCE(d.sumStatus1, 0) AS sumStatus1,
       COALESCE(d.sumStatus2, 0) AS sumStatus2,
       COALESCE(d.sumStatus3, 0) AS sumStatus3,
       COALESCE(d.sumStatus4, 0) AS sumStatus4,
       COALESCE(d.sumStatus5, 0) AS sumStatus5
FROM calendar_dt c
LEFT JOIN (
    SELECT DATE(uploadTime_beg) AS dt,
           SUM(field01) AS sumStatus1, ..., SUM(field05) AS sumStatus5
    FROM health_realdata
    WHERE DATE(uploadTime_beg) BETWEEN @week_start AND @week_end
    GROUP BY DATE(uploadTime_beg)
) d ON c.dt = d.dt
WHERE c.dt BETWEEN @week_start AND @week_end
ORDER BY c.dt;

關鍵點

• 使用 `DATE()` 而非 `DATE_FORMAT` 進行分組與比較，可用索引。
• `COALESCE` / `IFNULL` 將 `NULL` 轉為 `0`。
• 日曆表只需建立一次，永久復用。

MySQL 8.0+ 遞迴 CTE 替代日曆表

WITH RECURSIVE week_days AS (
    SELECT @week_start AS dt
    UNION ALL
    SELECT DATE_ADD(dt, INTERVAL 1 DAY) FROM week_days WHERE dt < @week_end
)
SELECT w.dt AS time,
       COALESCE(d.sumStatus1, 0) AS sumStatus1, ...
FROM week_days w
LEFT JOIN ( ... ) d ON w.dt = d.dt
ORDER BY w.dt;

總結

「左連接日曆表」是補齊缺失日期的標準模式，效能穩定且語義清晰。MySQL 8.0 以上可用 CTE 簡化，無需永久表。