MySQL 递归 CTE 怎样补齐日期序列

MySQL 递归 CTE 补齐日期序列的关键，是让 CTE 先生成一张“从最小日期到最大日期、每天一行”的日期表，再用它作为左表连接业务数据。这样，即使某一天没有销售记录，也能保留这一行，并用 COALESCE 将聚合结果显示为 0。

官方文档：https://dev.mysql.com/doc/refman/8.4/en/with.html

不要从业务明细直接分组后再想办法补空缺；先生成完整日期集合，再做 LEFT JOIN，是这个问题最稳定的查询结构。

先看一个有缺口的业务数据

假设有一张按天记录销售的 sales 表。示例只关心 sale_date 和 amount，不把文章扩展到建表索引或复杂的订单状态筛选。

-- 示例数据只用于说明日期缺口，不代表真实业务数据
CREATE TABLE sales (
  sale_date DATE NOT NULL,
  amount DECIMAL(12, 2) NOT NULL
);

-- 1 月 4 日、1 月 5 日和 1 月 7 日没有销售记录
INSERT INTO sales (sale_date, amount) VALUES
  ('2026-01-03', 100.00),
  ('2026-01-06', 50.00),
  ('2026-01-08', 180.00),
  ('2026-01-10', 5.00);

直接按日期分组只能得到 1 月 3 日、6 日、8 日和 10 日。缺失日期不是“销售额为 NULL 的行”，而是结果集中根本没有这些日期，所以单纯对聚合值使用 COALESCE 还不够。

用递归 CTE 生成连续日期

递归 CTE 有两个成员：第一个 SELECT 产生起点，第二个 SELECT 从上一轮日期增加一天。停止条件必须写在递归成员里，否则查询会持续生成日期，直到触发系统保护。

-- 非递归成员给出序列起点；空表时 MIN(date) 会是 NULL
WITH RECURSIVE dates (date) AS (
  SELECT MIN(sale_date)
  FROM sales

  UNION ALL

  -- 每轮只增加一天，直到不超过业务数据的最大日期
  SELECT date + INTERVAL 1 DAY
  FROM dates
  WHERE date + INTERVAL 1 DAY 

这段查询的边界是闭区间：最小日期和最大日期都会保留。WHERE 判断的是“下一天是否仍在终点以内”，因此不会多生成一天。MySQL 文档也把这种模式用于日期序列生成：非递归成员放入最低日期，递归成员逐日增加，达到最高日期后结束。

如果业务允许显式传入起止日期，推荐把边界放进一个单独的参数 CTE，避免每轮递归都重新表达业务条件：

-- 参数 CTE 将报表范围固定为闭区间 [start_date, end_date]
WITH RECURSIVE params AS (
  SELECT CAST('2026-01-03' AS DATE) AS start_date,
         CAST('2026-01-10' AS DATE) AS end_date
),
dates (date) AS (
  -- 递归起点来自参数，而不是业务明细
  SELECT start_date
  FROM params

  UNION ALL

  -- 终点包含在序列中，下一天超过终点时停止
  SELECT date + INTERVAL 1 DAY
  FROM dates
  CROSS JOIN params
  WHERE date 

使用参数范围时，调用方需要先保证 start_date 。如果允许反向输入，不要期待递归 CTE 自动修正方向；应在接口层拒绝，或者明确改成递减日期的另一条查询。

以日期序列为左表，缺失日期才会留下来

有了完整的 dates 后，把销售明细放在 LEFT JOIN 的右侧，再按日期聚合。没有匹配明细的日期仍来自左表，只是右表字段为空。

-- 日期序列是左表，因此没有销售的日期也会保留
WITH RECURSIVE dates (date) AS (
  SELECT MIN(sale_date)
  FROM sales
  UNION ALL
  SELECT date + INTERVAL 1 DAY
  FROM dates
  WHERE date + INTERVAL 1 DAY 

结果会保留 1 月 3 日到 1 月 10 日的每一天：有销售的日期显示汇总金额，缺失日期显示 0。这里的 COALESCE 是对聚合后的空值做展示层处理；真正补出日期行的是日期 CTE 和左连接。

三个容易写错的地方

把 INNER JOIN 写成 LEFT JOIN 的反面

如果写成 INNER JOIN，没有销售记录的日期会在连接阶段被过滤掉，最终又回到了“只显示有数据日期”的结果。日期序列必须是左表，业务明细才放到右表。

只写停止条件，不检查下一天

推荐判断“下一天是否小于等于终点”，这样闭区间更直观。如果直接判断当前日期，容易在边界上多生成一天或少生成一天。无论采用哪种写法，都要明确起点、终点和是否包含终点。

忽略空表和空范围

以 MIN(sale_date) 和 MAX(sale_date) 取边界时，空表会得到两个 NULL。这时递归起点本身就没有有效日期。对报表来说，更可控的做法是由调用方传入日期范围；如果必须从明细推导范围，就在外层明确把空结果当成“没有可生成的日期”。

递归深度与大范围日期的安全边界

递归 CTE 不是无限日期生成器。MySQL 的 cte_max_recursion_depth 用来限制 CTE 递归层数，文档列出的默认值是 1000；同时还可以用执行时间限制或递归查询中的行数限制，给异常范围增加第二道保护。

-- 只调整当前会话，避免影响之后新建的连接
SET SESSION cte_max_recursion_depth = 2000;

-- 给当前会话的 SELECT 设置一秒执行上限，数值单位是毫秒
SET SESSION max_execution_time = 1000;

-- 生产查询仍要保留真实的日期停止条件；配置不是替代条件
WITH RECURSIVE dates (date) AS (
  SELECT CAST('2026-01-01' AS DATE)
  UNION ALL
  SELECT date + INTERVAL 1 DAY
  FROM dates
  WHERE date 

如果日期范围可能跨越很多年，先确认报表是否真的需要每天一行。递归层数、临时表内存和最终聚合行数都会随范围增长；更长周期的报表可以考虑预先维护日期维度表，但那已经是另一种建模选择，不应靠调大递归上限掩盖范围失控。

最后记住这条查询结构

补齐日期的查询可以压缩成四个判断：先得到可靠的起止日期；再用递归 CTE 每次增加一天；以日期序列作为 LEFT JOIN 左表；最后对空聚合使用 COALESCE。递归成员负责“有没有下一天”，左连接负责“没有明细也保留日期”，两者解决的是不同问题。

相关问题

为什么只对 SUM 使用 COALESCE 仍然没有空日期？

因为 COALESCE 只能把已经存在的聚合结果从 NULL 改成 0，不能凭空创建结果行。必须先生成日期序列，再用 LEFT JOIN 保留空日期。

日期序列是否必须从业务表的 MIN 和 MAX 开始？

不是。固定报表范围通常更适合由参数直接传入，业务表的 MIN 和 MAX 更适合“覆盖现有数据范围”的查询。

递归 CTE 能不能补齐小时或分钟？

可以把递归步长改成对应的 INTERVAL，但行数会迅速增加，必须重新评估递归层数、执行时间和结果集大小。