我要评论在实际开发中，我们经常会遇到树形结构或图结构的数据需求，比如：
- 组织架构（部门 → 子部门）
- 商品分类（一级类目 → 二级类目 → …）
- 评论回复（评论 → 回复 → 回复的回复）
- 权限继承（角色 → 子角色）
- 路径查找（最短路径、依赖关系）
这些场景的核心问题是：如何高效查询具有层级/递归关系的数据？
postgresql 提供了强大的 with recursive（公共表表达式递归） 功能，是处理此类问题的标准 sql 解决方案。本文将从基础到实战，手把手教你掌握递归查询的精髓。
一、递归查询基础：cte 与with recursive
1.1 什么是 cte（common table expression）？
cte 是一种临时结果集，可被主查询引用，语法如下：
with cte_name as (
-- 查询语句
)
select * from cte_name;
优点：提升 sql 可读性、避免重复子查询、支持递归
1.2 递归 cte 的基本结构
with recursive cte_name as (
-- 1. 初始查询（锚点成员 anchor member）
select ... from table where ...
union [all]
-- 2. 递归查询（递归成员 recursive member）
select ... from table, cte_name where ...
)
select * from cte_name;
核心三要素：
| 部分 | 作用 | 注意事项 |
|---|---|---|
| 初始查询 | 定义递归起点（如根节点） | 必须能终止递归 |
| union [all] | 合并结果集 | union 去重，union all 保留重复（性能更高） |
| 递归查询 | 引用自身 cte，向下/向上遍历 | 必须有连接条件，避免无限循环 |
1.3 递归查询的建议
| 场景 | 推荐方案 |
|---|---|
| 标准树形查询（上下级） | with recursive + union all |
| 防循环 | 记录访问路径 array[id] + != all(path) |
| 限制深度 | 添加 depth 字段 + where depth < n |
| 高性能读 | 物化路径 / 闭包表（写少读多） |
| 返回树形 json | 自底向上聚合 + jsonb_build_object |
| python 集成 | 直接执行原生 sql（sqlalchemy 支持 cte） |
终极建议：
“90% 的树形查询，一个精心设计的 with recursive 就够了。”
只有在性能成为瓶颈时，才考虑物化路径等复杂模型。
二、经典场景实战：组织架构查询
假设有一张部门表 departments：
create table departments (
id serial primary key,
name varchar(100) not null,
parent_id integer references departments(id)
);
-- 插入示例数据
insert into departments (name, parent_id) values
('总公司', null),
('技术部', 1),
('产品部', 1),
('前端组', 2),
('后端组', 2),
('ios组', 2),
('设计组', 3);
2.1 查询“技术部”及其所有子部门（向下递归）
with recursive dept_tree as (
-- 锚点：找到“技术部”
select id, name, parent_id, 0 as level
from departments
where name = '技术部'
union all
-- 递归：找子部门
select d.id, d.name, d.parent_id, dt.level + 1
from departments d
inner join dept_tree dt on d.parent_id = dt.id
)
select
lpad('', level * 4, ' ') || name as hierarchy, -- 缩进显示层级
id, parent_id, level
from dept_tree
order by level;
输出结果：
hierarchy | id | parent_id | level
-----------------|----|-----------|------
技术部 | 2 | 1 | 0
前端组 | 4 | 2 | 1
后端组 | 5 | 2 | 1
ios组 | 6 | 2 | 1
技巧：lpad('', level * 4, ' ') 生成缩进，直观展示树形结构
2.2 查询“后端组”的完整上级路径（向上递归）
with recursive dept_path as (
-- 锚点：从“后端组”开始
select id, name, parent_id, 0 as level
from departments
where name = '后端组'
union all
-- 递归：找父部门
select d.id, d.name, d.parent_id, dp.level + 1
from departments d
inner join dept_path dp on d.id = dp.parent_id
where dp.parent_id is not null -- 避免 null 连接
)
select
repeat(' → ', level) || name as path_from_root
from dept_path
order by level desc; -- 从根到当前节点
输出结果：
path_from_root --------------------------- 总公司 → 技术部 → 后端组
三、高级技巧：控制递归深度与防环
3.1 限制递归深度（防止无限循环）
with recursive dept_limited as (
select id, name, parent_id, 1 as depth
from departments
where parent_id is null -- 从根开始
union all
select d.id, d.name, d.parent_id, dl.depth + 1
from departments d
inner join dept_limited dl on d.parent_id = dl.id
where dl.depth < 3 -- 最多查3层
)
select * from dept_limited;
3.2 检测并避免循环引用（图结构必备）
如果数据存在循环（如 a→b→c→a），递归会无限进行。解决方案：记录访问路径。
with recursive graph_traversal as (
-- 锚点
select
id,
name,
parent_id,
array[id] as path, -- 记录已访问节点
1 as depth
from departments
where name = '技术部'
union all
-- 递归
select
d.id,
d.name,
d.parent_id,
gt.path || d.id, -- 追加当前节点
gt.depth + 1
from departments d
inner join graph_traversal gt on d.parent_id = gt.id
where
d.id != all(gt.path) -- 关键：当前节点不在已访问路径中
and gt.depth < 10 -- 安全兜底
)
select * from graph_traversal;
d.id != all(gt.path) 确保不重复访问节点，彻底解决循环问题
3.3 反向应用：扁平数据转树形 json
postgresql 支持将递归结果直接转为 嵌套 json，适合 api 返回。如使用 jsonb_build_object 构建树
with recursive tree as (
-- 叶子节点（无子节点）
select
id,
name,
parent_id,
jsonb_build_object('id', id, 'name', name, 'children', '[]'::jsonb) as node
from categories c1
where not exists (
select 1 from categories c2 where c2.parent_id = c1.id
)
union all
-- 非叶子节点（聚合子节点）
select
p.id,
p.name,
p.parent_id,
jsonb_build_object(
'id', p.id,
'name', p.name,
'children', jsonb_agg(t.node)
) as node
from categories p
inner join tree t on t.parent_id = p.id
group by p.id, p.name, p.parent_id
)
select node
from tree
where parent_id is null; -- 返回根节点
输出 json：
{
"id": 1,
"name": "电子产品",
"children": [
{
"id": 2,
"name": "手机",
"children": [
{"id": 3, "name": "iphone", "children": []},
{"id": 4, "name": "华为", "children": []}
]
},
{
"id": 5,
"name": "电脑",
"children": [
{"id": 6, "name": "笔记本", "children": []}
]
}
]
}
此方法利用 自底向上聚合，天然避免循环，但要求数据为严格树形（无环）
四、实战案例：商品分类树
4.1 场景：电商商品分类（多级类目）
create table categories (
id serial primary key,
name varchar(100) not null,
parent_id integer references categories(id),
is_leaf boolean default false -- 是否叶子节点
);
-- 插入数据
insert into categories (name, parent_id, is_leaf) values
('电子产品', null, false),
('手机', 1, false),
('iphone', 2, true),
('华为', 2, true),
('电脑', 1, false),
('笔记本', 5, true);
4.2 查询“电子产品”下所有叶子类目（带完整路径）
with recursive category_tree as (
-- 锚点：根类目
select
id,
name,
parent_id,
name::text as full_path, -- 路径字符串
1 as level
from categories
where name = '电子产品'
union all
-- 递归：拼接路径
select
c.id,
c.name,
c.parent_id,
ct.full_path || ' > ' || c.name, -- 路径拼接
ct.level + 1
from categories c
inner join category_tree ct on c.parent_id = ct.id
)
select
full_path,
id,
level
from category_tree
where is_leaf = true; -- 只查叶子节点
输出：
full_path | id | level ----------------------------|----|------ 电子产品 > 手机 > iphone | 3 | 3 电子产品 > 手机 > 华为 | 4 | 3 电子产品 > 电脑 > 笔记本 | 6 | 3
4.3 python + sqlalchemy 实战
在 python 中使用递归查询：
from sqlalchemy import text
from sqlalchemy.orm import sessionmaker
def get_dept_tree(session, root_name):
query = text("""
with recursive dept_tree as (
select id, name, parent_id, 0 as level
from departments
where name = :root_name
union all
select d.id, d.name, d.parent_id, dt.level + 1
from departments d
inner join dept_tree dt on d.parent_id = dt.id
)
select * from dept_tree order by level;
""")
result = session.execute(query, {"root_name": root_name})
return result.fetchall()
# 使用
with session() as session:
tree = get_dept_tree(session, "技术部")
for row in tree:
print(f"{' ' * row.level}{row.name}")
五、性能优化：索引与执行计划
5.1 必建索引
-- 对 parent_id 建索引（递归连接的关键） create index idx_departments_parent_id on departments(parent_id); -- 如果常按 name 查询根节点 create index idx_departments_name on departments(name);
5.2 查看执行计划
explain (analyze, buffers) with recursive ... ; -- 你的递归查询
关键观察点：
- 是否使用了
index scan（而非seq scan） - 递归深度是否合理
- 内存使用（
buffers）
5.3 大数据量优化建议
| 问题 | 解决方案 |
|---|---|
| 递归太深（>100层） | 限制 depth < n，业务上通常不需要过深层级 |
| 数据量大（百万级） | 分页查询（先查id再关联）、物化路径（见下文） |
| 频繁查询 | 使用 物化路径（materialized path） 或 闭包表（closure table） |
六、替代方案对比：何时不用递归？
虽然 with recursive 很强大，但在某些场景下，其他模型更高效：
6.1 物化路径（materialized path）
在每条记录中存储完整路径：
alter table categories add column path text; -- 如 "/1/2/3/" -- 查询“手机”下所有子类目 select * from categories where path like '/1/2/%';
✅ 优点：查询极快（走索引）
❌ 缺点：移动节点时需更新大量 path
6.2 闭包表（closure table）
额外建一张表存储所有祖先-后代关系：
create table category_closure (
ancestor_id int,
descendant_id int,
depth int
);
-- 查询“手机”(id=2)的所有后代
select c.*
from categories c
join category_closure cl on c.id = cl.descendant_id
where cl.ancestor_id = 2;
✅ 优点：查询快，支持任意深度
❌ 缺点：写操作复杂，存储空间大
选择建议：
- 读多写少 + 深度固定 → 物化路径
- 频繁查询全路径 → 闭包表
- 通用场景 + 中小数据量 → with recursive
七、常见陷阱与避坑指南
陷阱 1：忘记where条件导致无限循环
-- 错误：缺少终止条件 select ... from table, cte where table.parent_id = cte.id -- 如果存在循环引用，永远停不下来！
✅ 解决：始终加上 depth < n 或路径检测
陷阱 2：使用union而非union all
union会去重，但递归中通常不需要（父子id唯一）- 性能损失高达 30%+
✅ 解决：除非明确需要去重，否则用 union all
陷阱 3：在递归部分使用聚合函数
-- 错误：递归成员不能包含聚合 select ..., count(*) from ... join cte ...
✅ 解决：先递归，再在外层聚合
以上就是postgresql优雅的进行递归查询的实战指南的详细内容，更多关于postgresql进行递归查询的资料请关注代码网其它相关文章！
发表评论