PostgreSQL 递归查询 WITH RECURSIVE

FreeGuideOnline 最新 2026-07-06

sql WITH RECURSIVE cte_name (column_list) AS ( -- 非递归部分（初始查询） SELECT … UNION [ALL] -- 递归部分（引用 cte_name 自身） SELECT … FROM cte_name, … ) SELECT * FROM cte_name;


整个递归过程分为三步：

1. **非递归部分（终止条件的基础数据）**：先执行一次，结果作为初始工作表。
2. **递归部分**：将前一步产生的结果作为输入，再次执行查询，生成新的行。每轮迭代的结果与之前的结果使用 `UNION`（或 `UNION ALL`）合并。
3. **终止条件**：当递归部分不再返回任何行时，整个递归停止。

> 注意：默认建议使用 `UNION ALL` 来保留重复行并提升性能。使用 `UNION` 会去重，可能导致递归无法正确终止或性能急剧下降。

## 基础示例：生成连续数列

最简单的递归场景是生成一个数字序列。以下查询生成 1～10 的整数：

```sql
WITH RECURSIVE numbers AS (
    SELECT 1 AS n          -- 初始值
    UNION ALL
    SELECT n + 1 FROM numbers WHERE n < 10  -- 递归条件
)
SELECT n FROM numbers;

执行过程：

初始行：n = 1
第一次递归：n + 1 = 2，满足 n < 10
...
当 n = 10 时，下一轮 n + 1 = 11，不满足 WHERE n < 10，返回空集，递归终止。

结果集即为 1 到 10。

经典案例：树形结构的查询

假设一张部门表 departments，记录了部门 ID、名称和父部门 ID：

CREATE TABLE departments (
    id          SERIAL PRIMARY KEY,
    name        TEXT NOT NULL,
    parent_id   INTEGER REFERENCES departments(id)
);

INSERT INTO departments (id, name, parent_id) VALUES
(1, '总公司', NULL),
(2, '研发部', 1),
(3, '市场部', 1),
(4, '后端组', 2),
(5, '前端组', 2),
(6, 'SEO组', 3);

查找某个节点的所有子节点

查询“研发部”（id = 2）的所有子孙部门：

WITH RECURSIVE subordinates AS (
    -- 非递归部分：直接子部门
    SELECT id, name, parent_id
    FROM departments
    WHERE parent_id = 2

    UNION ALL

    -- 递归部分：通过 subordinates 找到下一级
    SELECT d.id, d.name, d.parent_id
    FROM departments d
    JOIN subordinates s ON d.parent_id = s.id
)
SELECT * FROM subordinates;

结果将包含：研发部（本身？注意初始条件只选了 parent_id=2 的直接子部门，通常不包括自己。如果希望包含自身，可以在非递归部分直接查询 WHERE id = 2），然后不断向下展开，得到后端组、前端组，以及它们的子部门。

若要包含指定节点自身，可修改非递归部分为：

SELECT id, name, parent_id FROM departments WHERE id = 2

这样结果集就会以研发部为起点向下展开完整子树。

查找从叶子到根的路径（反向递归）

已知“SEO组”（id = 6），找到它的完整路径（从根到当前部门）：

WITH RECURSIVE path AS (
    -- 初始：当前部门
    SELECT id, name, parent_id
    FROM departments
    WHERE id = 6

    UNION ALL

    -- 递归：向上查找父部门
    SELECT d.id, d.name, d.parent_id
    FROM departments d
    JOIN path p ON d.id = p.parent_id
)
SELECT * FROM path;

结果返回：SEO组 → 市场部 → 总公司。如果需要从根开始的顺序，可以在外层查询使用 ORDER BY id 或依赖递归深度字段（下面会介绍）。

添加递归深度和路径信息

为了更清晰地展示层级关系，可以在递归中携带额外的计算列。

WITH RECURSIVE subtree AS (
    SELECT id, name, parent_id,
           0 AS depth,
           name::TEXT AS path
    FROM departments
    WHERE id = 1

    UNION ALL

    SELECT d.id, d.name, d.parent_id,
           s.depth + 1,
           s.path || ' → ' || d.name
    FROM departments d
    JOIN subtree s ON d.parent_id = s.id
)
SELECT depth, path FROM subtree ORDER BY depth, id;

depth：每递归一层加 1，表示层级深度。
path：逐级拼接名字，直观显示路径。

避免无限递归

递归 CTE 的一大陷阱是数据中出现环（例如 A 的父节点是 B，B 的父节点是 A），导致查询无限循环。PostgreSQL 默认不会强制终止，但可通过以下方法防范：

设置 `max_recursion_depth`（PG 14+）

从 PostgreSQL 14 开始，可以在会话级设置 max_recursion_depth 参数限制递归最大深度。

SET max_recursion_depth = 100;

在 CTE 中手动追踪路径并过滤环

使用数组或字符串记录访问过的节点，如果当前行已经出现在路径中则跳过。

WITH RECURSIVE safe_path AS (
    SELECT id, name, parent_id,
           ARRAY[id] AS visited
    FROM departments
    WHERE id = 1

    UNION ALL

    SELECT d.id, d.name, d.parent_id,
           s.visited || d.id
    FROM departments d
    JOIN safe_path s ON d.parent_id = s.id
    WHERE NOT d.id = ANY(s.visited)  -- 防止回头
)
SELECT * FROM safe_path;