PostgreSQL / 系统课时

使用递归公共表表达式

使用递归公共表表达式

本课解决树形结构无法用固定层数查询的问题。你将使用 WITH RECURSIVE 从一个根节点出发,逐层查出全部后代,并记录深度和访问路径。

学习目标

  • 理解递归 CTE 的锚点与递归项
  • 查询任意深度的树形数据
  • 使用路径避免循环引用导致无限递归

递归 CTE 的组成

递归 CTE 通常由两部分组成:锚点查询负责产生第一批行,递归查询根据上一轮结果继续寻找下一层。两部分通过 UNION ALL 连接。

PostgreSQL 会重复执行递归项,直到某一轮不再产生新行。递归并不是一次性跳到最深层,而是逐轮扩展结果集。

主示例:查询分类树

下面的 SQL 从根分类“商品”开始,查询全部子分类。depth 表示层级,path 保存已访问节点,用于排序和防止循环。

sql
CREATE TEMP TABLE categories (
    id integer PRIMARY KEY,
    parent_id integer,
    name text NOT NULL
);

INSERT INTO categories (id, parent_id, name) VALUES
(1, NULL, '商品'),
(2, 1, '数码'),
(3, 1, '家居'),
(4, 2, '手机'),
(5, 2, '电脑');

WITH RECURSIVE category_tree AS (
    -- 锚点:从指定根节点开始
    SELECT
        id,
        parent_id,
        name,
        0 AS depth,
        ARRAY[id] AS path
    FROM categories
    WHERE id = 1

    UNION ALL

    -- 递归项:查找上一层节点的直接子节点
    SELECT
        c.id,
        c.parent_id,
        c.name,
        ct.depth + 1,
        ct.path || c.id
    FROM categories AS c
    JOIN category_tree AS ct
      ON c.parent_id = ct.id
    WHERE NOT c.id = ANY(ct.path)
)
SELECT
    id,
    name,
    depth,
    array_to_string(path, ' > ') AS path
FROM category_tree
ORDER BY path;

预期结果

text
 id | name | depth |  path
----+------+-------+---------
  1 | 商品 |     0 | 1
  2 | 数码 |     1 | 1 > 2
  4 | 手机 |     2 | 1 > 2 > 4
  5 | 电脑 |     2 | 1 > 2 > 5
  3 | 家居 |     1 | 1 > 3

将锚点条件改为其他节点,就能查询对应子树。若要查祖先链,可以反向连接:让当前行的 parent_id 匹配上一轮节点的 id。

循环与终止条件

正常树结构不应出现环,但脏数据可能形成“节点 A 指向 B,B 又指向 A”。本例把访问过的 id 保存在数组中,并通过 NOT c.id = ANY(ct.path) 排除重复节点。

递归查询还可以增加最大深度,例如限制 ct.depth < 10。这不是替代数据校验,而是额外的安全边界。

性能注意点

递归连接频繁使用 parent_id,生产表通常应为该列建立索引。根节点范围越大、树越深,返回行数越多,因此应只选择业务真正需要的列,并在可能时限定根节点或深度。

查询完成后可使用 EXPLAIN ANALYZE 检查扫描方式和实际行数,避免对整棵大树做无边界遍历。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

使用递归公共表表达式

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通