🙏 我们需要您的帮助!SQLtest.online 没有广告,完全依靠捐款运行。哪怕一点点也很有帮助。 立即捐助 →
SQL 代码已复制到剪贴板

递归 CTE 示例:实际任务

第 6.6 课 · 阅读时间:约 9 分钟 · 练习题 ↓

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

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

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

递归 CTE 的应用:分类层级、组织架构、菜单树、物料清单和路径查找,均由锚点成员和递归步骤构成

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,而不是消失。

dayrentals
2005-05-30158
2005-05-31163
2005-06-010
......
2005-06-130
2005-06-1416
2005-06-15348

有三个要点:

  • 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。

part_nametotal_quantity
Frame1
Wheel2
Rim2
Spoke72
Bolt8

辐条: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 公里。

routetotal_km
Berlin,Prague,Vienna680
Berlin,Leipzig,Prague,Vienna770
Berlin,Leipzig,Munich,Vienna1020
Berlin,Prague,Leipzig,Munich,Vienna1430

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 子句,可以自动标记并终止环。

尝试解决以下任务,以巩固您在本课中学到的内容。

MySQL

  1. 创建日期表
  2. 计算周末天数
  3. 按月统计付款,包括空月份

DuckDB

  1. 34 号上车区域每日行程

Firebird

  1. 1993 年每月订单

MariaDB

  1. 每月新开始的研究项目

PostgreSQL

  1. 伊万诺沃每日出发航班

SQLite

  1. 每月开始的实验数

SQL Server

  1. 每个价格区间的商品数