0Pricing
SQL Academy · 课时

用于层次结构的递归 CTE

使用 WITH RECURSIVE 遍历层次化数据（组织结构图、串联评论、图遍历），并设置停止条件。

用于层次结构的递归 CTE 是 CoddyKit 上的免费 SQL Academy 课时。 这是第 4 节课，共 4 节。 你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。 这是 SQL Academy 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 SQL Academy 课程共包含 4 节课。

为什么需要递归

普通 SQL 无法遍历深度未知的树：父级的父级、子级的子级。递归 CTE 是标准 SQL 提供的解决方案。

结构

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

WITH RECURSIVE name AS (
  -- 1. Anchor query: seed rows
  SELECT ...
  UNION ALL
  -- 2. Recursive step: references the CTE itself
  SELECT ...
  FROM name JOIN ...
)
SELECT * FROM name;

遍历组织结构图

查找直接或间接向指定经理汇报的所有员工：

WITH RECURSIVE reports AS (
  -- anchor: the manager themself
  SELECT id, full_name, manager_id, 0 AS depth
  FROM employees WHERE id = 42

  UNION ALL

  -- recurse: people whose manager is in reports
  SELECT e.id, e.full_name, e.manager_id, r.depth + 1
  FROM employees e
  JOIN reports r ON r.id = e.manager_id
)
SELECT * FROM reports ORDER BY depth, full_name;

嵌套评论

从根节点遍历讨论树：

WITH RECURSIVE thread AS (
  SELECT id, parent_id, body, 0 AS depth, ARRAY[id] AS path
  FROM comments WHERE id = $1
  UNION ALL
  SELECT c.id, c.parent_id, c.body, t.depth + 1, t.path || c.id
  FROM comments c
  JOIN thread t ON c.parent_id = t.id
)
SELECT * FROM thread ORDER BY path;

终止条件

当递归步骤不再返回新行时，递归就会停止。

避免无限循环

如果图中存在环，请跟踪已访问的节点：

WITH RECURSIVE walk AS (
  SELECT id, ARRAY[id] AS path FROM nodes WHERE id = $1
  UNION ALL
  SELECT e.target_id, w.path || e.target_id
  FROM edges e
  JOIN walk w ON e.source_id = w.id
  WHERE e.target_id <> ALL(w.path)
)
SELECT * FROM walk;

数值序列

递归 CTE 还可以生成序列：

WITH RECURSIVE n(i) AS (
  VALUES (1)
  UNION ALL
  SELECT i + 1 FROM n WHERE i < 100
)
SELECT i, i*i AS square FROM n;

物料清单

将产品展开为所有组件，包括子装配件：

WITH RECURSIVE bom AS (
  SELECT part_id, sub_part_id, qty FROM parts WHERE part_id = $1
  UNION ALL
  SELECT p.part_id, p.sub_part_id, p.qty * bom.qty
  FROM parts p
  JOIN bom ON bom.sub_part_id = p.part_id
)
SELECT sub_part_id, SUM(qty) AS total_qty FROM bom GROUP BY sub_part_id;

深度限制

为确保安全，请限制递归深度：

WITH RECURSIVE tree AS (
  SELECT id, parent_id, 0 AS depth FROM nodes WHERE id = $1
  UNION ALL
  SELECT n.id, n.parent_id, t.depth + 1
  FROM nodes n JOIN tree t ON n.parent_id = t.id
  WHERE t.depth < 10
)
SELECT * FROM tree;

UNION 与 UNION ALL

通常选择 UNION ALL。UNION 会去重 — 当一个节点可以通过多条路径到达时，这很有用。

性能

递归 CTE 会迭代求值。每一步的“工作表”都是上一步产生的行。请为连接列建立索引。

回顾

递归 CTE 可以遍历层次结构和图。

  • 锚点 + UNION ALL + 递归步骤
  • 当递归步骤不返回任何行时停止
  • 使用路径数组打破环

快速检查

哪个关键字会将 CTE 变为递归 CTE？

常见问题解答

「用于层次结构的递归 CTE」课时是免费的吗？

是的 — 「用于层次结构的递归 CTE」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 SQL Academy 课程的其余内容，请升级到 CoddyKit PRO。 SQL Academy 课程共包含 4 节课。

「用于层次结构的递归 CTE」这节课中我会学到什么？

使用 WITH RECURSIVE 遍历层次化数据（组织结构图、串联评论、图遍历），并设置停止条件。 你通过在浏览器中直接运行的动手代码来练习 SQL Academy，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

学习 SQL Academy 需要有经验吗？

无需任何先前经验。CoddyKit 上的 SQL Academy 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。 这是第 4 节课，共 4 节。

「用于层次结构的递归 CTE」课时需要多长时间？

大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。

我能在这节 SQL Academy 课中编写并运行代码吗？

能。每节 SQL Academy 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。

此课程中的所有课时

  1. 标量、行与表子查询
  2. 相关与非相关子查询
  3. 公用表表达式（WITH）
  4. 用于层次结构的递归 CTE
← 返回 SQL Academy