在业务系统中，部门、分类、文件夹这类数据往往以自引用表的形式存储，每条记录通过parent_id指向自己的上级。当我们需要查询某一个节点下的所有子孙节点，或者还原从根到当前节点的完整路径时，普通的关联查询无法一次性展开未知层级的树形结构，必须依赖SQL的递归能力来实现嵌套遍历。
一、为什么普通查询搞不定多级目录
假设我们有一张category表，字段包括id、name和parent_id。parent_id为NULL表示根分类，其余记录指向上一层分类的id。如果只用内连接，例如把表和自身连一次，只能拿到直接子节点：
SELECT c1.id, c1.name, c2.name AS parent_name FROM category c1 LEFT JOIN category c2 ON c1.parent_id = c2.id WHERE c2.name = '电子产品';
上面这段最多查出“电子产品”的直接下级，比如“手机”“电脑”。可“手机”下面还有“智能手机”“老人机”，层级更深时，JOIN次数变成未知数，写死SQL不现实。应用层用代码循环查库虽然能跑，但每展开一层就打一次数据库，网络开销和查询次数都难以接受，且事务一致性也难保证。
更麻烦的是路径还原。比如用户点开了“智能手机”，面包屑要显示“电子产品 / 手机 / 智能手机”。这要求从当前节点一路向上回溯到根，或者从根向下拼出路径。关系型数据库若没有递归语法，就只能先取出全表再在内存里建树，数据量稍大就撑不住。因此，用SQL把树“展开”才是正解。
二、WITH RECURSIVE 的基本写法
SQL标准里的公共表表达式（CTE）配合RECURSIVE关键字，可以描述“先取锚点，再反复用上轮结果关联原表”的过程。以PostgreSQL和MySQL 8为例，查节点id=1下所有后代的写法如下：
WITH RECURSIVE sub_tree AS (
-- 锚点：起始节点
SELECT id, name, parent_id, 0 AS depth
FROM category
WHERE id = 1
UNION ALL
-- 递归：子节点关联上轮结果
SELECT c.id, c.name, c.parent_id, st.depth + 1
FROM category c
INNER JOIN sub_tree st ON c.parent_id = st.id
)
SELECT * FROM sub_tree;
这段代码分两部分。锚点查询先选出id=1那一行，depth记为0；UNION ALL后面的递归部分，每轮把sub_tree里已有的节点当作“父”，去category里找parent_id等于这些id的记录，并让depth加一。数据库会一直跑，直到某一轮找不到新行为止。
需要注意，UNION ALL不会去重，如果数据里有环（比如A的父是B，B的父又是A），递归将永远不结束。生产环境应在锚点之外加保护，例如限制depth小于二十，或者用临时表记录已访问id。另外，parent_id上必须有索引，否则每轮关联都是全表扫，层级深了极慢。
三、如何同时算出递归路径
仅仅列出节点还不够，很多时候要直接拿到“根/一级/二级”这样的路径字符串。我们可以在递归里用一个字段不断拼接名称。下面例子在MySQL 8中生成用斜杠分隔的路径：
WITH RECURSIVE path_tree AS (
SELECT id, name, parent_id, CAST(name AS CHAR(1000)) AS path
FROM category
WHERE parent_id IS NULL
UNION ALL
SELECT c.id, c.name, c.parent_id,
CONCAT(pt.path, '/', c.name) AS path
FROM category c
INNER JOIN path_tree pt ON c.parent_id = pt.id
)
SELECT id, name, path
FROM path_tree
WHERE id = 15;
锚点部分取所有根节点，path初始化为自身name；递归部分每次把上层path和当前name用斜杠连起来。这样查到id=15时，path字段已经是完整的“电子产品/手机/智能手机”。比起查出扁平数据再让后端拼，SQL里直接算路径减少了应用代码量，也避免了多次查询。
如果数据库对字符串长度有限制，比如CAST长度不够，可以改用TEXT类型或在外层再做一次处理。Oracle没有RECURSIVE语法，但可以用CONNECT BY PRIOR id = parent_id配合SYS_CONNECT_BY_PATH(name, '/')达到一样效果，思路都是“边遍历边累积”。
四、不同数据库的递归方案对比
虽然思想一致，但各库语法有差别。下面的表格列出常见实现方式：
| 数据库 | 递归写法 | 路径函数 | 备注 |
|---|---|---|---|
| PostgreSQL | WITH RECURSIVE | 用CONCAT自建 | 标准兼容好 |
| MySQL 8+ | WITH RECURSIVE | CONCAT | 5.7及以下不支持 |
| SQL Server | WITH cte AS (...) 加UNION ALL | 用CAST加+号拼接 | 不需写RECURSIVE词 |
| Oracle | CONNECT BY PRIOR | SYS_CONNECT_BY_PATH | 老牌树查语法 |
SQL Server的写法看起来没写RECURSIVE，但CTE里出现自身引用就自动按递归处理。Oracle的CONNECT BY更紧凑，PRIOR放哪边决定向上还是向下遍历，例如PRIOR parent_id = id就是自底向上找祖先，适合做面包屑。
从维护角度看，WITH RECURSIVE可读性更强，锚点和递归边界清晰，也方便加depth做层级控制。老系统若绑死Oracle，用CONNECT BY也够用，只是换库时语法迁移成本较高。
五、性能与避坑建议
递归查询最怕两张情况：一是parent_id没索引，每次JOIN都全表扫；二是数据有环却没退出条件。针对前者，请确认表上有类似下面的索引：
CREATE INDEX idx_category_parent ON category (parent_id);
针对环问题，可以在CTE里引入已访问集合，或者简单限制深度。例如只展开十层以内：
WITH RECURSIVE safe_tree AS (
SELECT id, name, parent_id, 0 AS depth
FROM category WHERE id = 1
UNION ALL
SELECT c.id, c.name, c.parent_id, st.depth + 1
FROM category c
INNER JOIN safe_tree st ON c.parent_id = st.id
WHERE st.depth < 10
)
SELECT * FROM safe_tree;
另一个坑是路径字段长度溢出，尤其是分类名很长且层级多时，CONCAT结果可能超长被截断。提前用较长的CHAR或TEXT，并在应用层校验。若目录极深、并发又高，还可以考虑把闭包表（closure table）冗余存储祖先-后代关系，用空间换时间，彻底免去递归。
总结来说，SQL处理多级目录嵌套查询的核心就是利用递归公共表表达式，从锚点出发循环关联自引用表，并在过程中累积路径或深度。只要加好索引、防住环、控住长度，就能在数据库内一行SQL搞定树形展开，不必来回折腾应用与数据库。