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 检查扫描方式和实际行数,避免对整棵大树做无边界遍历。
按自己的节奏,确认真正理解。