MySQL 查詢結果按日期自動填充缺失天數（補 0）的標準 SQL 寫法

需求

原表僅有離散日期記錄，需在指定區間內輸出**連續日期**，無記錄的天數金額/人數顯示 0。

核心思路：生成連續日期序列 + LEFT JOIN

-- 1. 創建臨時日期表（遞歸 CTE，MySQL 8.0+）
WITH RECURSIVE date_seq(dt) AS (
    SELECT '2015-09-15' UNION ALL
    SELECT DATE_ADD(dt, INTERVAL 1 DAY) FROM date_seq WHERE dt < '2015-09-30'
)
-- 2. 左連接業務表
SELECT 
    d.dt AS `date`,
    COALESCE(b.money, 0) AS money,
    COALESCE(b.num, 0) AS num
FROM date_seq d
LEFT JOIN billRec b ON b.date = d.dt
ORDER BY d.dt;

MySQL 5.7 兼容方案：數字表/輔助表

-- 永久數字表（一次創建永久使用）
CREATE TABLE nums(n INT PRIMARY KEY);
INSERT INTO nums VALUES (0),(1),(2)...(10000);

-- 查詢
SELECT 
    DATE_ADD('2015-09-15', INTERVAL n DAY) AS `date`,
    COALESCE(b.money, 0) AS money,
    COALESCE(b.num, 0) AS num
FROM nums
WHERE DATE_ADD('2015-09-15', INTERVAL n DAY) <= '2015-09-30'
LEFT JOIN billRec b ON b.date = DATE_ADD('2015-09-15', INTERVAL n DAY)
ORDER BY `date`;

應用層填充（不推薦但可行）

List result = new ArrayList<>();
LocalDate start = LocalDate.parse("2015-09-15");
LocalDate end = LocalDate.parse("2015-09-30");
Map map = dbRecords.stream().collect(Collectors.toMap(r -> r.getDate(), r -> r));

for (LocalDate d = start; !d.isAfter(end); d = d.plusDays(1)) {
    result.add(map.getOrDefault(d, new Record(d, 0, 0)));
}

總結

SQL 層面解決利用集合運算，性能最優、代碼最少、避免數據傳輸開銷。MySQL 8.0+ 首選 CTE，舊版本建立永久數字表。