在业务系统中，组织架构、商品类目、评论回复等数据常以父子关联形式存在，这类层级数据的最大难点在于深度不固定。使用递归SQL查询，尤其是基于公用表表达式（CTE）的写法，可以用一套语句把整棵树拍平，避免多层join或应用端循环查询。
什么是递归SQL与CTE
递归SQL是指一条查询在定义中引用了自身结果集，从而能够沿着某种关系反复迭代。现代关系型数据库如PostgreSQL、MySQL 8.0、SQL Server均支持使用WITH RECURSIVE声明递归公用表表达式。CTE本质上是一个临时命名结果集，递归版CTE由两部分组成：锚点成员和递归成员。
锚点成员负责取出初始行，通常是根节点；递归成员则引用CTE自身，通过连接条件不断取出子级。数据库引擎会循环执行递归部分，直到某次迭代不再产生新行，或者达到系统设定的最大递归深度。理解这一执行模型，是写好递归查询的前提。
基础语法与示例
下面以一张员工表为例，表中id为主键，manager_id指向直属上级。我们要查出某个领导下的所有下属及其层级深度。
WITH RECURSIVE sub_tree AS ( -- 锚点：找出根节点（这里假设根领导id为1） SELECT id, name, manager_id, 1 AS depth FROM employee WHERE id = 1 UNION ALL -- 递归：通过manager_id关联下级 SELECT e.id, e.name, e.manager_id, st.depth + 1 FROM employee e INNER JOIN sub_tree st ON e.manager_id = st.id ) SELECT * FROM sub_tree ORDER BY depth, id;
上述代码中，锚点取出id为1的员工，递归部分每执行一次就把直接下属加入结果，同时深度加一。UNION ALL保证保留所有行而不去重，性能优于UNION。最终外部查询按深度和id排序，得到一棵从上到下的树。
如果还想输出从根到当前节点的路径，可以在递归成员中拼接名称。例如在SELECT里增加st.path || ' > ' || e.name，锚点部分初始化为根名称。这样每行都带有完整层级路径，便于前端直接展示面包屑。
常见误区与避坑
最容易犯的错误是遗漏终止条件，导致递归无限循环。虽然多数数据库有最大递归次数限制，但接近上限时查询会极慢且耗费资源。务必确保递归连接条件能逐步缩小结果集，比如用主键关联且子级必然少于父级。
另一个误区是在大表上不加索引。递归查询每次迭代都要用manager_id去匹配，若该列无索引，复杂度会急剧上升。应为外键列建立索引，并尽量在锚点中用主键定位根节点。此外，循环引用（如A管B、B管A）也会让某些库陷入重复，需要在应用层或约束中防止。
递归与替代方案对比
在CTE普及前，开发者常用邻接表加应用端递归，或预计算路径串、闭包表。下表简要对比：
| 方案 | 优点 | 缺点 |
|---|---|---|
| 递归CTE | 语句自包含，实时计算，易读 | 深树性能依赖索引，部分旧库不支持 |
| 闭包表 | 查询极快，不受深度影响 | 写入需维护关系行，空间占用大 |
| 路径字段 | 简单 like 可查子树 | 更新路径成本高，易不一致 |
从维护成本看，递归SQL最适合中等规模、结构变动频繁的层级数据。若系统对读取性能极度敏感且层级固定，闭包表更优。选型时应结合业务写读比例。
实战中的优化技巧
当只需要某一子树且深度有限时，可在递归成员中加入WHERE st.depth < 5之类的限制，提前截断。也可在最终查询中用ARRAY_AGG等窗口函数做聚合，直接生成树形JSON返回给接口。
对于MySQL用户，注意WITH RECURSIVE从8.0才开始支持，老版本需用存储过程模拟。SQL Server则可用OPTION (MAXRECURSION 100)显式控制上限，避免意外爆栈。掌握这些细节，递归查询才能稳定服务于生产环境。