文章目录
- 一、递归查询基础：CTE 与 `WITH RECURSIVE`
- 1.1 什么是 CTE（Common Table Expression）？
- 1.2 递归 CTE 的基本结构
- 1.3 递归查询的建议
- 二、经典场景实战：组织架构查询
- 2.1 查询“技术部”及其所有子部门（向下递归）
- 2.2 查询“后端组”的完整上级路径（向上递归）
- 三、高级技巧：控制递归深度与防环
- 3.1 限制递归深度（防止无限循环）
- 3.2 检测并避免循环引用（图结构必备）
- 3.3 反向应用：扁平数据转树形 JSON
- 四、实战案例：商品分类树
- 4.1 场景：电商商品分类（多级类目）
- 4.2 查询“电子产品”下所有叶子类目（带完整路径）
- 4.3 Python + SQLAlchemy 实战
- 五、性能优化：索引与执行计划
- 5.1 必建索引
- 5.2 查看执行计划
- 5.3 大数据量优化建议
- 六、替代方案对比：何时不用递归？
- 6.1 物化路径（Materialized Path）
- 6.2 闭包表（Closure Table）
- 七、常见陷阱与避坑指南
- 陷阱 1：忘记 `WHERE` 条件导致无限循环
- 陷阱 2：使用 `UNION` 而非 `UNION ALL`
- 陷阱 3：在递归部分使用聚合函数
在实际开发中，我们经常会遇到树形结构或图结构的数据需求，比如：
- 组织架构（部门 → 子部门）
- 商品分类（一级类目 → 二级类目 → …）
- 评论回复（评论 → 回复 → 回复的回复）
- 权限继承（角色 → 子角色）
- 路径查找（最短路径、依赖关系）
这些场景的核心问题是：如何高效查询具有层级/递归关系的数据？
PostgreSQL 提供了强大的WITH RECURSIVE（公共表表达式递归）功能，是处理此类问题的标准 SQL 解决方案。本文将从基础到实战，手把手教你掌握递归查询的精髓。
一、递归查询基础：CTE 与WITH RECURSIVE
1.1 什么是 CTE（Common Table Expression）？
CTE 是一种临时结果集，可被主查询引用，语法如下：
WITHcte_nameAS(-- 查询语句)SELECT*FROMcte_name;优点：提升 SQL 可读性、避免重复子查询、支持递归
1.2 递归 CTE 的基本结构
WITHRECURSIVE cte_nameAS(-- 1. 初始查询（锚点成员 Anchor Member）SELECT...FROMtableWHERE...UNION[ALL]-- 2. 递归查询（递归成员 Recursive Member）SELECT...FROMtable,cte_nameWHERE...)SELECT*FROMcte_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：
CREATETABLEdepartments(idSERIALPRIMARYKEY,nameVARCHAR(100)NOTNULL,parent_idINTEGERREFERENCESdepartments(id));-- 插入示例数据INSERTINTOdepartments(name,parent_id)VALUES('总公司',NULL),('技术部',1),('产品部',1),('前端组',2),('后端组',2),('iOS组',2),('设计组',3);2.1 查询“技术部”及其所有子部门（向下递归）
WITHRECURSIVE dept_treeAS(-- 锚点：找到“技术部”SELECTid,name,parent_id,0ASlevelFROMdepartmentsWHEREname='技术部'UNIONALL-- 递归：找子部门SELECTd.id,d.name,d.parent_id,dt.level+1FROMdepartments dINNERJOINdept_tree dtONd.parent_id=dt.id)SELECTLPAD('',level*4,' ')||nameAShierarchy,-- 缩进显示层级id,parent_id,levelFROMdept_treeORDERBYlevel;输出结果：
hierarchy | id | parent_id | level -----------------|----|-----------|------ 技术部 | 2 | 1 | 0 前端组 | 4 | 2 | 1 后端组 | 5 | 2 | 1 iOS组 | 6 | 2 | 1技巧：
LPAD('', level * 4, ' ')生成缩进，直观展示树形结构
2.2 查询“后端组”的完整上级路径（向上递归）
WITHRECURSIVE dept_pathAS(-- 锚点：从“后端组”开始SELECTid,name,parent_id,0ASlevelFROMdepartmentsWHEREname='后端组'UNIONALL-- 递归：找父部门SELECTd.id,d.name,d.parent_id,dp.level+1FROMdepartments dINNERJOINdept_path dpONd.id=dp.parent_idWHEREdp.parent_idISNOTNULL-- 避免 NULL 连接)SELECTREPEAT(' → ',level)||nameASpath_from_rootFROMdept_pathORDERBYlevelDESC;-- 从根到当前节点输出结果：
path_from_root --------------------------- 总公司 → 技术部 → 后端组三、高级技巧：控制递归深度与防环
3.1 限制递归深度（防止无限循环）
WITHRECURSIVE dept_limitedAS(SELECTid,name,parent_id,1ASdepthFROMdepartmentsWHEREparent_idISNULL-- 从根开始UNIONALLSELECTd.id,d.name,d.parent_id,dl.depth+1FROMdepartments dINNERJOINdept_limited dlONd.parent_id=dl.idWHEREdl.depth<3-- 最多查3层)SELECT*FROMdept_limited;3.2 检测并避免循环引用（图结构必备）
如果数据存在循环（如 A→B→C→A），递归会无限进行。解决方案：记录访问路径。
WITHRECURSIVE graph_traversalAS(-- 锚点SELECTid,name,parent_id,ARRAY[id]ASpath,-- 记录已访问节点1ASdepthFROMdepartmentsWHEREname='技术部'UNIONALL-- 递归SELECTd.id,d.name,d.parent_id,gt.path||d.id,-- 追加当前节点gt.depth+1FROMdepartments dINNERJOINgraph_traversal gtONd.parent_id=gt.idWHEREd.id!=ALL(gt.path)-- 关键：当前节点不在已访问路径中ANDgt.depth<10-- 安全兜底)SELECT*FROMgraph_traversal;
d.id != ALL(gt.path)确保不重复访问节点，彻底解决循环问题
3.3 反向应用：扁平数据转树形 JSON
PostgreSQL 支持将递归结果直接转为嵌套 JSON，适合 API 返回。如使用jsonb_build_object构建树
WITHRECURSIVE treeAS(-- 叶子节点（无子节点）SELECTid,name,parent_id,jsonb_build_object('id',id,'name',name,'children','[]'::jsonb)ASnodeFROMcategories c1WHERENOTEXISTS(SELECT1FROMcategories c2WHEREc2.parent_id=c1.id)UNIONALL-- 非叶子节点（聚合子节点）SELECTp.id,p.name,p.parent_id,jsonb_build_object('id',p.id,'name',p.name,'children',jsonb_agg(t.node))ASnodeFROMcategories pINNERJOINtree tONt.parent_id=p.idGROUPBYp.id,p.name,p.parent_id)SELECTnodeFROMtreeWHEREparent_idISNULL;-- 返回根节点输出 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 场景：电商商品分类（多级类目）
CREATETABLEcategories(idSERIALPRIMARYKEY,nameVARCHAR(100)NOTNULL,parent_idINTEGERREFERENCEScategories(id),is_leafBOOLEANDEFAULTfalse-- 是否叶子节点);-- 插入数据INSERTINTOcategories(name,parent_id,is_leaf)VALUES('电子产品',NULL,false),('手机',1,false),('iPhone',2,true),('华为',2,true),('电脑',1,false),('笔记本',5,true);4.2 查询“电子产品”下所有叶子类目（带完整路径）
WITHRECURSIVE category_treeAS(-- 锚点：根类目SELECTid,name,parent_id,name::TEXTASfull_path,-- 路径字符串1ASlevelFROMcategoriesWHEREname='电子产品'UNIONALL-- 递归：拼接路径SELECTc.id,c.name,c.parent_id,ct.full_path||' > '||c.name,-- 路径拼接ct.level+1FROMcategories cINNERJOINcategory_tree ctONc.parent_id=ct.id)SELECTfull_path,id,levelFROMcategory_treeWHEREis_leaf=true;-- 只查叶子节点输出：
full_path | id | level ----------------------------|----|------ 电子产品 > 手机 > iPhone | 3 | 3 电子产品 > 手机 > 华为 | 4 | 3 电子产品 > 电脑 > 笔记本 | 6 | 34.3 Python + SQLAlchemy 实战
在 Python 中使用递归查询：
fromsqlalchemyimporttextfromsqlalchemy.ormimportsessionmakerdefget_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})returnresult.fetchall()# 使用withSession()assession:tree=get_dept_tree(session,"技术部")forrowintree:print(f"{' '*row.level}{row.name}")五、性能优化：索引与执行计划
5.1 必建索引
-- 对 parent_id 建索引（递归连接的关键）CREATEINDEXidx_departments_parent_idONdepartments(parent_id);-- 如果常按 name 查询根节点CREATEINDEXidx_departments_nameONdepartments(name);5.2 查看执行计划
EXPLAIN(ANALYZE,BUFFERS)WITHRECURSIVE...;-- 你的递归查询关键观察点：
- 是否使用了
Index Scan（而非Seq Scan） - 递归深度是否合理
- 内存使用（
Buffers）
5.3 大数据量优化建议
| 问题 | 解决方案 |
|---|---|
| 递归太深（>100层） | 限制depth < N，业务上通常不需要过深层级 |
| 数据量大（百万级） | 分页查询（先查ID再关联）、物化路径（见下文） |
| 频繁查询 | 使用物化路径（Materialized Path）或闭包表（Closure Table） |
六、替代方案对比：何时不用递归？
虽然WITH RECURSIVE很强大，但在某些场景下，其他模型更高效：
6.1 物化路径（Materialized Path）
在每条记录中存储完整路径：
ALTERTABLEcategoriesADDCOLUMNpathTEXT;-- 如 "/1/2/3/"-- 查询“手机”下所有子类目SELECT*FROMcategoriesWHEREpathLIKE'/1/2/%';✅ 优点：查询极快（走索引）
❌ 缺点：移动节点时需更新大量 path
6.2 闭包表（Closure Table）
额外建一张表存储所有祖先-后代关系：
CREATETABLEcategory_closure(ancestor_idINT,descendant_idINT,depthINT);-- 查询“手机”(id=2)的所有后代SELECTc.*FROMcategories cJOINcategory_closure clONc.id=cl.descendant_idWHEREcl.ancestor_id=2;✅ 优点：查询快，支持任意深度
❌ 缺点：写操作复杂，存储空间大
📌选择建议：
- 读多写少 + 深度固定→ 物化路径
- 频繁查询全路径→ 闭包表
- 通用场景 + 中小数据量→
WITH RECURSIVE
七、常见陷阱与避坑指南
陷阱 1：忘记WHERE条件导致无限循环
-- 错误：缺少终止条件SELECT...FROMtable,cteWHEREtable.parent_id=cte.id-- 如果存在循环引用，永远停不下来！✅ 解决：始终加上depth < N或路径检测
陷阱 2：使用UNION而非UNION ALL
UNION会去重，但递归中通常不需要（父子ID唯一）- 性能损失高达 30%+
✅ 解决：除非明确需要去重，否则用UNION ALL
陷阱 3：在递归部分使用聚合函数
-- 错误：递归成员不能包含聚合SELECT...,COUNT(*)FROM...JOINcte...✅ 解决：先递归，再在外层聚合
