SQL 递归 CTE：查询层级数据

第 6.5 课 · 阅读时间：约 9 分钟 · 练习题 ↓

SQL 递归 CTE 是引用自身的公用表表达式，因此一条查询就能遍历任意深度的层级结构：组织架构、分类树、评论楼层。在本课中，你将学习 WITH RECURSIVE 语法、锚点成员和递归成员如何协同工作、如何查找某个员工的所有下属或所有上级、如何在树中构建路径，以及如何阻止失控的递归。

在上一课中，CTE 是查询中一个有名字、只执行一次的步骤。层级数据需要更多能力。如果每个员工行都保存着其上级的 id，一次 JOIN 能找到直接下属，第二次 JOIN 找到下一层，以此类推，但你事先并不知道一共有多少层。递归 CTE 会替你重复连接，直到再也找不到新的行。

示例使用 MySQL 8.0 语法，也可以在 MariaDB 10.2+ 上运行。SQLtest 上的示例数据库是只读的，因此请在练习场中选择不含示例数据的数据库来运行示例：每个示例都会创建自己的表。

递归 CTE 如何工作

递归 CTE 由两部分组成，用 UNION ALL 连接：

WITH RECURSIVE cte_name AS (
    -- 锚点成员：起始行
    SELECT ...
    FROM table_name
    WHERE start_condition

    UNION ALL

    -- 递归成员：根据上一层找到的下一层
    SELECT ...
    FROM table_name
    INNER JOIN cte_name ON join_condition
)
SELECT * FROM cte_name;

• WITH RECURSIVE 告诉数据库这个 CTE 会引用自身。
• 锚点成员（anchor member）只执行一次，返回起始行，例如最高负责人。
• 递归成员（recursive member）按名称引用 CTE。每次执行时，它只能看到上一次迭代产生的行。
• 当某次迭代没有返回任何行时，递归停止。所有迭代产生的行共同构成结果。

最简单的例子：数字序列

WITH RECURSIVE numbers AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1
    FROM numbers
    WHERE n < 5
)
SELECT n FROM numbers;

结果：5 行，数字为 1、2、3、4、5。

锚点返回 1。第一次迭代读取 1 并返回 2，下一次返回 3，以此类推。当上一次迭代产生了 5 时，条件 n < 5 为假，递归成员不再返回任何行，查询结束。WHERE 中的条件就是终止条件：没有它，递归永远不会停止。

示例数据：组织架构

表中的层级通常以邻接表（adjacency list）的形式存储：每一行都引用同一张表中的父行。这里 manager_id 指向另一名员工，而 CEO 没有上级：

CREATE TABLE employee (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    manager_id INT,
    title VARCHAR(100)
);

INSERT INTO employee VALUES
    (1, 'Alice Johnson', NULL, 'CEO'),
    (2, 'Bob Smith', 1, 'VP of Engineering'),
    (3, 'Carol White', 1, 'VP of Sales'),
    (4, 'David Brown', 2, 'Engineering Manager'),
    (5, 'Eve Davis', 2, 'Engineering Manager'),
    (6, 'Frank Miller', 3, 'Sales Manager'),
    (7, 'Grace Wilson', 4, 'Senior Developer'),
    (8, 'Henry Moore', 4, 'Developer'),
    (9, 'Ivy Taylor', 5, 'Developer'),
    (10, 'Jack Anderson', 6, 'Sales Representative');

查找所有下属

谁直接或间接向 Bob Smith 汇报？锚点选出 Bob；递归成员查找上级位于上一层的员工：

WITH RECURSIVE subordinates AS (
    SELECT employee_id, employee_name, title, 0 AS level
    FROM employee
    WHERE employee_name = 'Bob Smith'

    UNION ALL

    SELECT e.employee_id, e.employee_name, e.title, s.level + 1
    FROM employee AS e
    INNER JOIN subordinates AS s ON e.manager_id = s.employee_id
)
SELECT employee_id, employee_name, title, level
FROM subordinates
ORDER BY level, employee_name;

结果：6 行：Bob Smith 位于第 0 层，David Brown 和 Eve Davis 位于第 1 层，Grace Wilson、Henry Moore 和 Ivy Taylor 位于第 2 层。

level 列记录迭代次数：它在锚点中为 0，每向下一层就加 1。如果要排除 Bob 本人，在最终查询中加上 WHERE level > 0。

构建带路径的完整树

要显示完整的组织架构，从没有上级的员工开始，收集从顶端到每个人的路径：

WITH RECURSIVE org_chart AS (
    SELECT
        employee_id,
        employee_name,
        title,
        0 AS level,
        CAST(employee_name AS CHAR(500)) AS path
    FROM employee
    WHERE manager_id IS NULL

    UNION ALL

    SELECT
        e.employee_id,
        e.employee_name,
        e.title,
        oc.level + 1,
        CONCAT(oc.path, ' > ', e.employee_name)
    FROM employee AS e
    INNER JOIN org_chart AS oc ON e.manager_id = oc.employee_id
)
SELECT
    CONCAT(REPEAT('- ', level), employee_name) AS hierarchy,
    title,
    path
FROM org_chart
ORDER BY path;

结果：全部 10 名员工按树的顺序排列，每人都带有其上方的上级链。

有两个关键细节：

• 按路径排序会把每名员工排在其上级之后，因此结果读起来就像一棵树。
• 锚点中的 CAST(... AS CHAR(500))。 递归 CTE 的列类型取自锚点成员。如果不做类型转换，path 会得到 employee_name 的类型 VARCHAR(100)，一旦路径超过 100 个字符，MySQL 就会报错“Data too long for column 'path'”并停止。对于在递归中不断变长的文本列，要在锚点中就把它定义得足够宽。

向上遍历：某个员工的所有上级

同样的方法也可以反方向使用。要找到 Ivy Taylor 之上的上级链，从她开始，把每一行与其父行连接：连接条件反过来写。

WITH RECURSIVE managers AS (
    SELECT employee_id, employee_name, manager_id, 0 AS steps_up
    FROM employee
    WHERE employee_name = 'Ivy Taylor'

    UNION ALL

    SELECT e.employee_id, e.employee_name, e.manager_id, m.steps_up + 1
    FROM employee AS e
    INNER JOIN managers AS m ON e.employee_id = m.manager_id
)
SELECT employee_name, steps_up
FROM managers
ORDER BY steps_up;

结果：4 行：Ivy Taylor（0）、Eve Davis（1）、Bob Smith（2）、Alice Johnson（3）。

递归在 Alice Johnson 处停止：她的 manager_id 是 NULL，因此连接找不到任何行。

阻止失控的递归

当某次迭代没有返回任何行时，递归 CTE 就会结束。有两种情况会打破这一规则：缺少终止条件（例如不带 WHERE 的数字序列），以及数据中的循环，例如误把 A 设为 B 的上级、又把 B 设为 A 的上级。这时同样的行会被一次又一次地找到。

限制深度

在递归成员中添加对层级的条件：

WITH RECURSIVE subordinates AS (
    SELECT employee_id, employee_name, 0 AS level
    FROM employee
    WHERE manager_id IS NULL

    UNION ALL

    SELECT e.employee_id, e.employee_name, s.level + 1
    FROM employee AS e
    INNER JOIN subordinates AS s ON e.manager_id = s.employee_id
    WHERE s.level < 2
)
SELECT employee_name, level
FROM subordinates;

结果：6 行：CEO、两位副总裁和三位经理；第 2 层以下不再展开。

MySQL 还有一个安全上限：变量 cte_max_recursion_depth（默认 1000）。需要更多迭代的查询会报错“Recursive query aborted after 1001 iterations”。如果确实需要很深的递归，可以用 SET SESSION cte_max_recursion_depth = 10000; 调高上限。MariaDB 的行为不同：达到 max_recursive_iterations 时它会不报错地停止，并返回到目前为止找到的行，因此不完整的结果很容易被忽略。

跳过已访问的行

限制深度可以终止循环，但仍会返回重复的行。要从根本上阻止循环，就保存已访问的 id 列表，不再进入路径中已经出现过的行：

WITH RECURSIVE org_chart AS (
    SELECT employee_id, employee_name, CAST(employee_id AS CHAR(500)) AS visited
    FROM employee
    WHERE manager_id IS NULL

    UNION ALL

    SELECT e.employee_id, e.employee_name, CONCAT(oc.visited, ',', e.employee_id)
    FROM employee AS e
    INNER JOIN org_chart AS oc ON e.manager_id = oc.employee_id
    WHERE FIND_IN_SET(e.employee_id, oc.visited) = 0
)
SELECT employee_name, visited
FROM org_chart;

结果：10 行；例如，Ivy Taylor 的 visited = 1,2,5,9。

FIND_IN_SET() 返回某个值在逗号分隔列表中的位置，如果不在列表中则返回 0。PostgreSQL 14+ 为此提供了内置的 CYCLE 子句。

规则与数据库之间的差异

• 递归成员必须恰好引用 CTE 一次，并且要在 FROM 或 JOIN 中引用，而不能在子查询中引用。
• 在 MySQL 中，递归成员不能使用聚合函数、窗口函数、GROUP BY、ORDER BY 或 DISTINCT。请在最终查询中进行聚合。
• UNION ALL 保留所有行，是通常的选择。UNION 会去除重复行，因此当重复的行完全相同时，它会顺带终止循环。

为了提升性能，请为递归连接所用的列（例如 manager_id）建立索引。

要点：

• 递归 CTE 引用自身，借助 WITH RECURSIVE 遍历任意深度的层级。
• 锚点成员返回起始行；递归成员根据上一层找到下一层；当某次迭代不再返回行时，递归停止。
• 连接条件 e.manager_id = cte.employee_id 向下遍历树；e.employee_id = cte.manager_id 向上遍历。
• level 计数器和 path 列显示每一行的位置；对不断变长的文本列，要在锚点中做类型转换。
• 用深度限制或已访问 id 列表保护查询；MySQL 在 cte_max_recursion_depth 次迭代后停止。

常见问题

什么是 SQL 中的递归 CTE？

递归 CTE 是引用自身的公用表表达式。它由返回起始行的锚点成员和反复执行的递归成员组成，递归成员每次都处理上一次迭代产生的行，直到不再出现新行为止。它用于层级和图结构：组织架构、分类树、路线。

如何阻止递归 CTE 无限递归？

在递归成员中加入终止条件，例如 WHERE level < 10，并通过记录已访问的 id 来防止数据中的循环。此外，MySQL 会在 cte_max_recursion_depth 次迭代（默认 1000）后中止查询，SQL Server 在 MAXRECURSION（默认 100）后中止；MariaDB 则会在 max_recursive_iterations 后静默停止。

MySQL 支持递归 CTE 吗？

支持，从 MySQL 8.0 和 MariaDB 10.2 开始，使用 WITH RECURSIVE 语法。旧版本的 MySQL 没有 CTE；当时通常用存储过程或固定次数的自连接来查询层级数据。

为什么我的递归 CTE 报错“Data too long for column”？

递归 CTE 的列类型取自锚点成员。如果递归成员构造出更长的字符串（例如路径），它就放不下了。在锚点中把该列转换为更宽的类型，例如 CAST(name AS CHAR(500))。