第 6.6 课 · 阅读时间：约 9 分钟

递归 CTE 不只用于组织架构。在本课中，你将把 WITH RECURSIVE 应用到四个常见任务上：生成日期序列以填补每日报表中的空缺、对分类树的每一层汇总销售额、把物料清单展开成完整的零件列表，以及在带环的道路网络中找出两座城市之间的所有路线。

递归 CTE 示例：实际任务

在上一课中，你学习了递归 CTE 的工作方式：锚点成员返回起始行，递归成员找到下一层，当不再出现新行时递归停止。在本课中，同样的模式变成了解决日常问题的工具。每个示例都展示了一种可复用的技巧：生成序列、祖先–后代对、逐层相乘的数量，以及带环检测的路径。

第一个示例在 Sakila 示例数据库上运行。其他示例会创建自己的表，因此请在练习场中选择不含示例数据的数据库来运行。语法为 MySQL 8.0，也可以在 MariaDB 10.2+ 上运行。

1. 生成日期序列以填补空缺

用 GROUP BY 生成的“每日租赁量”报表只显示有租赁记录的日子。没有租赁的日子会直接消失，根据这样的数据画出的图表会掩盖空缺。解决办法是先生成包含所有日期的日历，再把数据连接到日历上。

日历由递归 CTE 生成：锚点返回第一天，每次迭代增加一天，直到最后一天：

WITH RECURSIVE calendar AS (
    SELECT DATE('2005-05-28') AS day
    UNION ALL
    SELECT day + INTERVAL 1 DAY
    FROM calendar
    WHERE day < '2005-06-16'
)
SELECT c.day, COUNT(r.rental_id) AS rentals
FROM calendar AS c
LEFT JOIN rental AS r
    ON r.rental_date >= c.day
   AND r.rental_date < c.day + INTERVAL 1 DAY
GROUP BY c.day
ORDER BY c.day;

结果：20 行，2005 年 5 月 28 日到 6 月 16 日每天一行。6 月 1 日到 6 月 13 日没有租赁，这些日子显示为 0，而不是消失。

有三个要点：

• LEFT JOIN 保留了没有租赁的日子；COUNT(r.rental_id) 只统计真实的租赁记录，因此空白日期得到 0。
• 连接条件使用范围（>= day 且 < day + 1 天），而不是 DATE(r.rental_date) = c.day，这样仍然可以使用 rental_date 上的索引。
• 同样的模式可以生成任何序列：数字、小时、月份。超过 1000 步的序列需要调高 cte_max_recursion_depth。

2. 汇总整棵子树

在分类树中，销售记录属于具体的分类，但报表需要每个分支的汇总：“Computers”要包含笔记本电脑和台式机，“Electronics”则要包含全部。

CREATE TABLE product_category (
    category_id INT PRIMARY KEY,
    parent_id INT,
    name VARCHAR(50) NOT NULL
);

INSERT INTO product_category VALUES
    (1, NULL, 'Electronics'),
    (2, 1, 'Computers'),
    (3, 1, 'Phones'),
    (4, 2, 'Laptops'),
    (5, 2, 'Desktops'),
    (6, 3, 'Smartphones');

CREATE TABLE sale (
    sale_id INT PRIMARY KEY,
    category_id INT NOT NULL,
    amount DECIMAL(10, 2) NOT NULL
);

INSERT INTO sale VALUES
    (1, 4, 1200), (2, 4, 800), (3, 5, 700),
    (4, 6, 500), (5, 6, 300), (6, 3, 150);

技巧是先构建所有的祖先–后代对。锚点把每个分类与自身配对；每次迭代再加入已找到的后代的子分类：

WITH RECURSIVE subtree AS (
    SELECT category_id AS ancestor_id, category_id
    FROM product_category

    UNION ALL

    SELECT s.ancestor_id, c.category_id
    FROM subtree AS s
    INNER JOIN product_category AS c ON c.parent_id = s.category_id
)
SELECT a.name, SUM(sl.amount) AS total_sales
FROM subtree AS s
INNER JOIN product_category AS a ON a.category_id = s.ancestor_id
LEFT JOIN sale AS sl ON sl.category_id = s.category_id
GROUP BY a.category_id, a.name
ORDER BY a.category_id;

结果：Electronics 3650.00、Computers 2700.00、Phones 950.00、Laptops 2000.00、Desktops 700.00、Smartphones 800.00。

每笔销售会为其所属分类计一次，并为每个祖先各计一次。“Phones”得到 950：150 是直接记在该分类下的销售，另外 800 来自“Smartphones”。聚合在最终查询中进行，因为 MySQL 不允许在递归成员中使用 GROUP BY 或聚合函数。

3. 展开物料清单

物料清单（BOM，bill of materials）列出了产品由哪些部件组成。部件本身又有部件，同一个零件可能出现在多个分支中。要为一辆自行车订购零件，你需要知道每种零件的总数量：

CREATE TABLE part (
    part_id INT PRIMARY KEY,
    part_name VARCHAR(50) NOT NULL
);

INSERT INTO part VALUES
    (1, 'Bicycle'), (2, 'Frame'), (3, 'Wheel'),
    (4, 'Rim'), (5, 'Spoke'), (6, 'Bolt');

CREATE TABLE bom (
    parent_part_id INT,
    child_part_id INT,
    quantity INT NOT NULL,
    PRIMARY KEY (parent_part_id, child_part_id)
);

INSERT INTO bom VALUES
    (1, 2, 1),   -- 一辆自行车有 1 个车架
    (1, 3, 2),   -- 和 2 个车轮
    (2, 6, 4),   -- 一个车架有 4 个螺栓
    (3, 4, 1),   -- 一个车轮有 1 个轮圈
    (3, 5, 36),  -- 36 根辐条
    (3, 6, 2);   -- 和 2 个螺栓

在每一层，部件的数量都要乘以其父部件的数量：

WITH RECURSIVE explosion AS (
    SELECT child_part_id AS part_id, quantity
    FROM bom
    WHERE parent_part_id = 1

    UNION ALL

    SELECT b.child_part_id, e.quantity * b.quantity
    FROM explosion AS e
    INNER JOIN bom AS b ON b.parent_part_id = e.part_id
)
SELECT p.part_name, SUM(e.quantity) AS total_quantity
FROM explosion AS e
INNER JOIN part AS p ON p.part_id = e.part_id
GROUP BY p.part_id, p.part_name
ORDER BY p.part_id;

结果：Frame 1、Wheel 2、Rim 2、Spoke 72、Bolt 8。

辐条：2 个车轮 × 36 = 72。螺栓来自两个分支：车架中有 4 个，车轮中有 2 × 2 = 4 个，SUM 把它们加起来得到 8。如果只想得到需要采购而不是组装的零件，就只保留没有子部件的行：加上 WHERE NOT EXISTS (SELECT 1 FROM bom AS c WHERE c.parent_part_id = e.part_id)。

4. 带环图中的路线

树中到达每个节点只有一条路径。道路网络则是一个图：道路是双向的，并且会形成环，所以简单的递归会无休止地绕圈。下面找出从柏林到维也纳的所有路线：

CREATE TABLE road (
    from_city VARCHAR(30),
    to_city VARCHAR(30),
    distance_km INT NOT NULL,
    PRIMARY KEY (from_city, to_city)
);

INSERT INTO road VALUES
    ('Berlin', 'Prague', 350),
    ('Berlin', 'Leipzig', 190),
    ('Leipzig', 'Prague', 250),
    ('Prague', 'Vienna', 330),
    ('Leipzig', 'Munich', 430),
    ('Munich', 'Vienna', 400);

每条道路只存储一次，因此先用普通 CTE edge 补上反方向。递归 CTE trip 每次把路线延长一段道路，跳过路线中已经经过的城市，并在路线到达维也纳后不再延长：

WITH RECURSIVE edge AS (
    SELECT from_city, to_city, distance_km FROM road
    UNION ALL
    SELECT to_city, from_city, distance_km FROM road
),
trip AS (
    SELECT
        to_city AS city,
        CAST(CONCAT(from_city, ',', to_city) AS CHAR(500)) AS route,
        distance_km AS total_km
    FROM edge
    WHERE from_city = 'Berlin'

    UNION ALL

    SELECT e.to_city, CONCAT(t.route, ',', e.to_city), t.total_km + e.distance_km
    FROM trip AS t
    INNER JOIN edge AS e ON e.from_city = t.city
    WHERE FIND_IN_SET(e.to_city, t.route) = 0
      AND t.city <> 'Vienna'
)
SELECT route, total_km
FROM trip
WHERE city = 'Vienna'
ORDER BY total_km;

结果：4 条路线；最短的是柏林、布拉格、维也纳，共 680 公里。

FIND_IN_SET(e.to_city, t.route) = 0 就是上一课中的环检测：路线永远不会回到已经经过的城市。注意 WITH RECURSIVE 只写一次，其后的 CTE 既可以是普通的，也可以是递归的。

这条查询会列出所有可能的路线，而路线数量会随着网络规模迅速增长。它适用于小型图，但在大型道路网络中，最短路径要用专门的算法来求解，例如图数据库或 PostgreSQL 扩展 pgRouting。

什么情况下递归 CTE 不是最佳工具

• 层级很深且读取频繁的树。 如果层级被读取的次数远多于被修改的次数，可以存储额外的结构：物化路径（/1/2/4/），或保存所有祖先–后代对的闭包表（closure table），就像把上面的 subtree CTE 保存成一张表。这样查询子树就只是一个简单的 JOIN，无需递归。
• 大型图。 枚举路线的代价呈指数增长；请改用图算法。
• 层级固定且很少。 对于“员工及其上级”这类问题，一次自连接（第 5.7 课）更简单。

要点：

• 递归 CTE 可以生成序列：用 LEFT JOIN 连接的日期日历可以填补报表中的空缺。
• 递归 CTE 构建的祖先–后代对可以用来汇总整棵子树的数值。
• 在物料清单中，数量逐层相乘，并在最终查询中用 GROUP BY 求和。
• 在带环的图中，把已访问的节点保存在路径里，并用 FIND_IN_SET() 跳过它们。
• 聚合应放在最终查询中：MySQL 不允许在递归成员中进行聚合。
• 对于大型图或读取非常频繁的树，可以考虑闭包表或图算法。

常见问题

如何在 MySQL 中生成日期序列？

使用递归 CTE：WITH RECURSIVE calendar AS (SELECT DATE('2024-01-01') AS day UNION ALL SELECT day + INTERVAL 1 DAY FROM calendar WHERE day < '2024-01-31') SELECT day FROM calendar;。PostgreSQL 还提供内置函数 generate_series()。

如何在 SQL 报表中填补缺失的日期？

先用递归 CTE 生成包含所有日期的日历，再用 LEFT JOIN 把数据连接上来，并统计被连接表中的某一列，例如 COUNT(r.rental_id)。没有数据的日期会得到 0，而不是消失。

如何计算某个节点及其所有后代的合计？

用递归 CTE 构建祖先–后代对：锚点把每个节点与自身配对，递归成员加入每个后代的子节点。然后把数值连接到后代上，并按祖先 GROUP BY。

用递归 CTE 遍历图时如何避免环？

把已访问节点的路径保存在一个字符串列中，并在递归成员中加入类似 FIND_IN_SET(next_node, path) = 0 的条件。PostgreSQL 14+ 还提供 CYCLE 子句，可以自动标记并终止环。