SQL如何填补日期分组中缺失的零值-SQL-PHP中文网

sql本身不会自动补全缺失日期，group by只对实际存在数据分组；必须显式构造完整日期序列（如递归cte或generate_series），再left join原始数据，并用coalesce(sum(), 0)填充null值。

sql如何填补日期分组中缺失的零值

GROUP BY 日期后出现空缺天数怎么办

直接说结论：SQL 本身不会自动补全缺失日期，GROUP BY 只对实际存在的数据分组，没数据的日期天然不出现。想让结果里有“0”值，必须显式构造完整日期序列，再用 LEFT JOIN 或 RIGHT JOIN 补齐。

用递归 CTE 生成连续日期序列（PostgreSQL / SQL Server / SQLite 3.8.3+）

这是最通用、可控性强的做法，尤其适合需要指定起止范围的场景。注意不同数据库语法细节差异：

PostgreSQL 和 SQL Server 支持 GENERATE_SERIES()（更简洁），但 CTE 兼容性更好
MySQL 8.0+ 才支持递归 CTE；5.7 及之前只能靠临时表或应用层生成
Oracle 用户优先考虑 CONNECT BY，不过 CTE 在 12c+ 也支持

示例（PostgreSQL）：

WITH date_series AS (
  SELECT CURRENT_DATE - INTERVAL '6 days' AS dt
  UNION ALL
  SELECT dt + INTERVAL '1 day'
  FROM date_series
  WHERE dt = CURRENT_DATE - INTERVAL '6 days'
  GROUP BY DATE(created_at)
) t ON d.dt::date = t.day;

LEFT JOIN 后必须用 COALESCE() 处理 NULL

很多人写了 LEFT JOIN 却忘了把 NULL 转成 0，结果看到的是 NULL 而不是预期的零值。这不是显示问题，是逻辑缺失。

COALESCE(t.cnt, 0) 是标准写法，各数据库都支持
别用 IFNULL()（MySQL）或 ISNULL()（SQL Server）替代，会降低可移植性
如果聚合字段是 SUM() 或 AVG()，同样要套 COALESCE()，否则缺数据的行结果仍是 NULL

WHERE 条件写在 JOIN 前还是 JOIN 后影响结果完整性

这是最容易踩的坑：把过滤条件放在右表（事实表）的 WHERE 子句里，会把左表补上的“空日期”又过滤掉。

✅ 正确：过滤条件写在子查询或 ON 中（如示例里的 WHERE created_at >= ... 在子查询内）
❌ 错误：WHERE t.day IS NOT NULL OR t.day IS NULL 这类看似无害的条件，只要引用右表字段，就会让 LEFT JOIN 退化为 INNER JOIN
小技巧：把右表聚合逻辑封装成子查询或 CTE，能天然隔离过滤范围，降低出错概率

补日期这事本质是“先造全集，再找交集”，顺序乱了，零就没了。