递归 CTE：任意深度层次结构

自连接（模块 5）仅查找层次结构的一层：子级 → 父母。但如果层次结构的深度为 3、5 或 n 层，会发生什么情况？而且你事先不知道有多少？您需要递归 CTE： CTE 指的是迭代地“扩展”结果。

在 ecommerce 数据集中，categories 表有 3 个根（Elettronica、Libri、Casa）各有 2 个子级——一棵 2 层树。这是一个简单的案例但足以学习该模式。

语法

SQL

WITH RECURSIVE nome (col1, col2, …) AS (
  -- (1) Termine BASE: le righe di partenza
  SELECT …
  FROM   …
  WHERE  …

  UNION ALL

  -- (2) Termine RICORSIVO: si appoggia a "nome" stesso per espandere
  SELECT …
  FROM   tabella  t
  JOIN   nome    n  ON …
)
SELECT * FROM nome;

它在实践中是如何运作的：

PostgreSQL 执行一次基本术语。
它使用刚刚生成的行执行递归项 “输入”（不是整个 CTE：仅增量）。
重复步骤 2，直到递归项产生 0 个新行。
最终结果是所有步骤的UNION ALL。

示例 1：具有深度和路径的树

SQL

WITH RECURSIVE tree AS (
  -- base: le radici (parent_id IS NULL)
  SELECT id,
         name,
         parent_id,
         1                 AS depth,
         name              AS path
  FROM   categories
  WHERE  parent_id IS NULL

  UNION ALL

  -- ricorsivo: per ogni categoria che ha come parent una riga già in tree
  SELECT c.id,
         c.name,
         c.parent_id,
         t.depth + 1,
         t.path || ' > ' || c.name
  FROM   categories c
  JOIN   tree       t ON c.parent_id = t.id
)
SELECT id, name, depth, path
FROM   tree
ORDER BY path;

第一次迭代生成 3 行（第 1 层的根）。第二个迭代找到 6 个子级（级别 2）。第三次迭代发现什么也没做。

示例2：给定一个节点，找到它的根

“自上而下 → 自下而上”的变体：我们向下传播根的 id 树。

SQL

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

  UNION ALL

  SELECT c.id,
         c.name,
         c.parent_id,
         t.root_id,    -- propago lo stesso id_radice
         t.root_name   -- e nome
  FROM   categories c
  JOIN   tree       t ON c.parent_id = t.id
)
SELECT id, name, root_name
FROM   tree
ORDER BY id;

尝试一下

锻炼#sql.m7.l4.e1

尝试：0加载中...

使用名为“tree”的递归 CTE 来展平类别层次结构。显示 id、名称、深度（1 表示根，2 表示子项等）和路径（例如“Elettronica > Audio”）。按路径排序。

正在加载编辑器...

显示提示

递归项中的 JOIN 将 c.parent_id 链接到树中已存在的行 (t.id)。

3 次尝试后可用的解决方案

复习练习

锻炼#sql.m7.l4.e2

尝试：0加载中...

对于每个类别，显示 id、名称和 root_name（位于其所属树顶部的根类别的名称）。根具有 root_name = name。按 ID 订购。

正在加载编辑器...

显示提示

将 root_name 从父级 (t.root_name) 传播到子级 (c)。

3 次尝试后可用的解决方案