SQL Academy · 课时

遍历分类树

Q: 「遍历分类树」这节课中我会学到什么？

完整展开父子树 你通过在浏览器中直接运行的动手代码来练习 SQL Academy，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

完整展开父子树

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

什么是分类树

许多现实世界的数据集都具有父子关系。产品目录可能包含这样的分类：电子产品 → 手机 → 智能手机。每个节点都有一个父节点，从而形成树状结构。

在 SQL 中，这通常存储为自引用表：每一行都有一个 id，以及一个指向同一张表中另一行的 parent_id。

CREATE TABLE categories (
  id       INT PRIMARY KEY,
  name     VARCHAR(100) NOT NULL,
  parent_id INT REFERENCES categories(id)
);

示例分类数据

让我们填充一个小型分类树。根节点的 parent_id = NULL，因为它没有父节点。其他每个节点都通过非空的 parent_id 指向自己的父节点。

INSERT INTO categories (id, name, parent_id) VALUES
  (1, 'Electronics',   NULL),
  (2, 'Phones',         1),
  (3, 'Laptops',        1),
  (4, 'Smartphones',    2),
  (5, 'Feature Phones', 2),
  (6, 'Gaming Laptops', 3),
  (7, 'Ultrabooks',     3);

简单查询的问题

普通的 SELECT 每次只能获取一层。要深入到三层，您需要执行三个独立查询或使用三次自连接；随着树不断扩展，这种方式会变得难以管理。

WITH RECURSIVE 通过允许查询引用自身的输出解决了这个问题，使查询能够逐层遍历，直到找不到新行。

-- This only shows direct children of Electronics (level 1)
SELECT id, name
FROM   categories
WHERE  parent_id = 1;

WITH RECURSIVE 的组成

递归 CTE 由 UNION ALL 分隔的两部分组成：

1. 锚点成员 — 一个提供起始行的普通 SELECT。

2. 递归成员 — 一个将 CTE 与自身连接的 SELECT，每次迭代生成下一级。

引擎会重复执行递归成员，直到它返回零行。

WITH RECURSIVE cte AS (
  -- Anchor: starting rows
  SELECT ...
  UNION ALL
  -- Recursive: join cte to base table
  SELECT ... FROM base_table JOIN cte ON ...
)
SELECT * FROM cte;

从根节点遍历完整树

从根节点（其中 parent_id IS NULL）开始，向下遍历每个后代。递归成员根据父子关系，将每个累积的行重新连接到 categories。

WITH RECURSIVE category_tree AS (
  -- Anchor: root nodes
  SELECT id, name, parent_id, 1 AS depth
  FROM   categories
  WHERE  parent_id IS NULL

  UNION ALL

  -- Recursive: children of current level
  SELECT c.id, c.name, c.parent_id, ct.depth + 1
  FROM   categories      c
  JOIN   category_tree   ct ON ct.id = c.parent_id
)
SELECT id, name, depth
FROM   category_tree
ORDER  BY depth, id;

跟踪路径

记录从根节点到每个节点的完整路径很有帮助。随着递归深入，我们可以通过拼接祖先名称来构建一个 path 字符串。

这样便于显示类似 电子产品 / 手机 / 智能手机 这样的面包屑路径。

WITH RECURSIVE category_tree AS (
  SELECT id, name, parent_id,
         name AS path
  FROM   categories
  WHERE  parent_id IS NULL

  UNION ALL

  SELECT c.id, c.name, c.parent_id,
         ct.path || ' / ' || c.name
  FROM   categories    c
  JOIN   category_tree ct ON ct.id = c.parent_id
)
SELECT id, name, path
FROM   category_tree
ORDER  BY path;

从特定节点开始

您不必从根节点开始。通过更改锚点中的 WHERE 子句，您可以遍历任意节点的子树。这里我们从手机（编号 = 2）开始，获取它的所有后代。

WITH RECURSIVE subtree AS (
  SELECT id, name, parent_id, 0 AS depth
  FROM   categories
  WHERE  id = 2          -- start at Phones

  UNION ALL

  SELECT c.id, c.name, c.parent_id, s.depth + 1
  FROM   categories c
  JOIN   subtree    s ON s.id = c.parent_id
)
SELECT id, name, depth
FROM   subtree
ORDER  BY depth, id;

向上遍历：查找所有祖先

也可以反向遍历树——从叶节点向上遍历到根节点。只需反转连接方式，沿着 parent_id 向上而不是向下查找即可。当您需要已知叶节点的完整面包屑路径时，这非常有用。

WITH RECURSIVE ancestors AS (
  SELECT id, name, parent_id
  FROM   categories
  WHERE  id = 4          -- start at Smartphones

  UNION ALL

  SELECT c.id, c.name, c.parent_id
  FROM   categories c
  JOIN   ancestors  a ON a.parent_id = c.id
)
SELECT id, name
FROM   ancestors
ORDER  BY id;

添加缩进显示

一种常见的用户界面模式是以视觉方式缩进子节点。我们可以将 REPEAT（或 LPAD）与 depth 列结合，在每个名称前添加空格，从而生成基于文本的树形视图。

WITH RECURSIVE category_tree AS (
  SELECT id, name, parent_id, 0 AS depth
  FROM   categories
  WHERE  parent_id IS NULL

  UNION ALL

  SELECT c.id, c.name, c.parent_id, ct.depth + 1
  FROM   categories    c
  JOIN   category_tree ct ON ct.id = c.parent_id
)
SELECT
  REPEAT('    ', depth) || name AS indented_name,
  depth
FROM   category_tree
ORDER  BY path;

防止无限循环

如果数据中包含循环（A 是 B 的父节点，而 B 又是 A 的父节点），递归将永远运行并导致崩溃。您可以在数组中跟踪已访问的标识符，并在当前标识符已经存在时停止递归，从而防止这种情况。

WITH RECURSIVE safe_tree AS (
  SELECT id, name, parent_id,
         ARRAY[id] AS visited
  FROM   categories
  WHERE  parent_id IS NULL

  UNION ALL

  SELECT c.id, c.name, c.parent_id,
         st.visited || c.id
  FROM   categories c
  JOIN   safe_tree  st ON st.id = c.parent_id
  WHERE  c.id <> ALL(st.visited)   -- stop if already seen
)
SELECT id, name FROM safe_tree;

统计每个节点的后代数量

获得完整的树后，您可以对其进行聚合。这里我们通过将子行按祖先列表重新分组，统计每个节点拥有的后代数量。这对于在导航菜单中的类别名称旁显示项目数量很有用。

WITH RECURSIVE category_tree AS (
  SELECT id, name, parent_id, id AS root_id
  FROM   categories
  WHERE  parent_id IS NULL

  UNION ALL

  SELECT c.id, c.name, c.parent_id, ct.root_id
  FROM   categories    c
  JOIN   category_tree ct ON ct.id = c.parent_id
)
SELECT
  root_id,
  COUNT(*) - 1 AS descendant_count
FROM   category_tree
GROUP  BY root_id
ORDER  BY root_id;

快速检查

测试您对递归类别树查询的理解。

课程回顾

在本课中，您学习了如何使用 WITH RECURSIVE 遍历自引用的类别表。

要点：

- 锚点成员选择起始节点（通常是根节点）。

- 递归成员将 CTE 重新连接到基础表，以查找下一级。

- 添加 depth 列，跟踪每个节点所处的层级深度。

- 构建 path 字符串，生成面包屑路径。

- 通过反向沿着 parent_id 向上遍历，查找所有祖先。

- 使用 visited 数组，防止脏数据中的循环。

常见问题解答

「遍历分类树」课时是免费的吗？

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

「遍历分类树」这节课中我会学到什么？

完整展开父子树你通过在浏览器中直接运行的动手代码来练习 SQL Academy，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

学习 SQL Academy 需要有经验吗？

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

「遍历分类树」课时需要多长时间？

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

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

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

此课程中的所有课时

← 返回 SQL Academy