递归查询通过WITH RECURSIVE实现，先执行非递归部分获取初始节点，再迭代连接结果与原表生成下一层级，直至无新数据；常用于树形结构，需注意终止条件与环路检测。
PostgreSQL 的递归查询通过 CTE（Common Table Expression） 实现，使用 WITH RECURSIVE 关键字。它特别适合处理树形结构或层级数据，比如组织架构、分类目录、评论回复链等。
递归 CTE 的基本结构
一个递归 CTE 由两个部分组成：非递归项（初始查询）和递归项，用 UNION [ALL] 连接：
WITH RECURSIVE cte_name AS (
-- 非递归部分（基础查询）
SELECT ...
FROM table
WHERE 条件（通常是顶层节点）
UNION ALL
-- 递归部分
SELECT ...
FROM table
JOIN cte_name ON 连接条件
)
SELECT * FROM cte_name;
递归执行机制详解
PostgreSQL 执行递归 CTE 并不是简单地“循环调用”，而是采用迭代方式，逐步构建结果集：
- 第1步：执行非递归部分，得到初始结果集，放入临时工作表中，同时作为输出的一部分。
- 第2步：将上一轮的结果作为“输入”，执行递归部分的查询，生成下一层级的数据。
- 第3步：将新生成的数据追加到结果集中，并更新工作表为本次输出。
- 重复第2-3步，直到某次递归查询返回空结果（即没有更多匹配行），递归停止。
整个过程是逐层展开的，类似广度优先搜索（BFS），每一层代表一次递归深度。
实际例子：组织架构中的上下级关系
假设有一个员工表 employees(id, name, manager_id)，其中 manager_id 指向上级：
WITH RECURSIVE org_tree AS (
-- 基础：从 CEO 开始（manager_id 为 NULL）
SELECT id, name, manager_id, 0 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- 递归：查找所有下属
SELECT e.id, e.name, e.manager_id, ot.level + 1
FROM employees e
JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT * FROM org_tree;
执行流程：
- 先查出 CEO（level=0）
- 然后查所有 manager_id 等于 CEO id 的员工（level=1）
- 再查这些员工的直接下属（level=2）
- 继续下去，直到没有更多下属为止
关键注意事项
递归查询虽然强大，但必须注意以下几点：
- 必须有终止条件，否则会无限循环。通常依赖数据结构自然结束（如叶子节点无子项）。
- 使用 UNION 可去重，UNION ALL 保留重复（性能更好，常用）。
- 避免循环引用（如 A → B → C → A），会导致无限递归。可用
ARRAY记录路径来检测环：
WITH RECURSIVE path_check AS (
SELECT id, name, ARRAY[id] AS path
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, pc.path || e.id
FROM employees e
JOIN path_check pc ON e.manager_id = pc.id
WHERE e.id != ALL(pc.path) -- 防止环路
)
SELECT * FROM path_check;
基本上就这些。PostgreSQL 的递归 CTE 是处理层级数据的利器，理解其迭代执行机制有助于写出高效且安全的查询。不复杂但容易忽略的是终止条件和环路检测。
