大家好，我是小耶，写功课只是为了我踩过的坑，你们别再踩了！
你有没有写过这样的代码：查组织架构，先查顶层部门，再循环查子部门，每层写一个for循环。部门深度是5层就写5个循环，深度不确定就写while循环不断查库。
这种写法，代码难看、性能差、还容易出bug。每次看到这种代码我都想问一句：为什么不用递归CTE？
递归CTE是SQL标准中处理树形结构的官方解法，MySQL 8.0+、PostgreSQL、SQL Server都原生支持。树形结构查询有好几种写法，每种写法各有优劣：
| 写法 | 优点 | 缺点 |
| 循环查库 | 直观，容易理解 | 代码冗长，性能差，N次查询 |
| 递归CTE | 标准解法，一次查询，支持任意深度 | 语法稍复杂，需注意性能 |
| 闭包表 | 查询极快 | 维护成本高，适合读多写少 |
| 路径枚举 | 查询快，易于理解 | 更新路径成本高 |
递归CTE可能是树形查询的最优解——语法简单清晰、一次搞定、不需要额外维护表结构。我把三个最常用的场景拆开讲，看完就能上手。
一、先搞清楚递归CTE是什么
递归CTE（Recursive Common Table Expression）说白了就是一个能“自己调用自己”的临时查询结果集。
它由两部分组成：
- 锚点（Anchor） ：告诉数据库“从哪开始找”。比如查部门树的时候，先找到最顶层的那个部门——
WHERE parent_id IS NULL。 - 递归成员（Recursive Member） ：告诉数据库“怎么继续往下找”。比如通过当前层的
id去关联子部门的parent_id——ON d.parent_id = dt.id。
两部分用UNION ALL连接起来。数据库执行的时候，先跑锚点拿到第一层结果，然后拿着这些结果去跑递归成员，拿到第二层，再拿第二层去跑第三层……直到再也找不到新数据为止。
整个流程可以理解成：从起点出发，每次拿当前查到的结果去查下一层，一层层往下展开，直到触底为止。 就像玩一个“点开一个文件夹，自动展开它里面所有子文件夹”的游戏，只不过这个游戏是数据库帮你自动完成的。
二、场景一：遍历组织架构树——最基础的用法
假设有一张部门表department，字段为id、name、parent_id。要查出某个部门及其所有下级：
WITH RECURSIVE dept_tree AS (
-- 锚点：从根部门开始
SELECT id, name, parent_id, 0 AS depth
FROM department
WHERE id = 1 -- 指定根节点ID
UNION ALL
-- 递归：找子部门
SELECT d.id, d.name, d.parent_id, dt.depth + 1
FROM department d
INNER JOIN dept_tree dt ON d.parent_id = dt.id
)
SELECT * FROM dept_tree ORDER BY depth, id;
depth字段记录层级深度，方便在应用层做缩进展示。ORDER BY depth保证了父级在子级之前输出。
为什么这比循环查库好？ 一次查询搞定所有层级，不用在应用层写循环，不用反复建立数据库连接，性能提升肉眼可见。
三、场景二：面包屑导航——从叶子节点往上找
电商网站的商品分类、博客的评论嵌套、系统的菜单路径——这些都需要“从当前位置追溯到根”。
比如给一个商品分类ID，生成“首页 > 手机 > 智能手机 > iPhone 15”这样的面包屑：
WITH RECURSIVE breadcrumb AS (
-- 锚点：从当前分类开始
SELECT id, name, parent_id, 1 AS level
FROM category
WHERE id = 123 -- 指定当前分类ID
UNION ALL
-- 递归：往上找父级
SELECT c.id, c.name, c.parent_id, b.level + 1
FROM category c
INNER JOIN breadcrumb b ON c.id = b.parent_id
)
SELECT * FROM breadcrumb ORDER BY level DESC;
ORDER BY level DESC让根节点排在前面，直接就能渲染面包屑。一层SQL搞定原本需要递归查询或多次查库才能完成的事情。
四、场景三：带路径的完整子树——行政区域查询
有时候不仅要查出所有下级节点，还要知道每个节点的完整路径。比如查询某个省下的所有市、区、街道：
WITH RECURSIVE region_tree AS (
-- 锚点：从指定省份开始
SELECT id, name, parent_id, 0 AS depth,
CAST(name AS CHAR(1000)) AS path
FROM region
WHERE id = 440000 -- 广东省
UNION ALL
-- 递归：找下级行政区
SELECT r.id, r.name, r.parent_id, rt.depth + 1,
CONCAT(rt.path, ' > ', r.name)
FROM region r
INNER JOIN region_tree rt ON r.parent_id = rt.id
)
SELECT * FROM region_tree ORDER BY depth, id;
path字段用CAST指定了足够长度，CONCAT逐层拼接，最终得到类似“广东省 > 广州市 > 天河区”的完整路径。
这个模式在电商的SPU-SKU层级、BOM物料清单、组织权限树等场景中都非常实用。
五、递归CTE的性能陷阱与避坑指南
递归CTE虽强，但用不好也会踩坑。
陷阱1：缺少索引，每层全表扫描
递归查询的每一层都会执行一次JOIN。如果parent_id没有索引，每一层都要扫描全表，性能急剧下降。
解法：确保parent_id上有索引。对于频繁查询树形结构的表，这是必须的。
陷阱2：锚点写错，查不出数据
锚点必须明确指定根节点。用WHERE parent_id IS NULL或WHERE id = 1都可以，但要确保和业务数据一致。如果业务中用parent_id = 0表示根节点，锚点就要写成WHERE parent_id = 0。
陷阱3：数据中存在环，导致无限递归
如果数据中出现A.parent_id = B.id且B.parent_id = A.id这样的环，递归CTE会无限执行下去。
解法：限制递归深度。在递归成员中添加WHERE dt.depth < 20这样的条件，或在最外层加LIMIT。大多数数据库也提供了MAXRECURSION选项来控制最大迭代次数。
陷阱4：大数据量下性能下降
当树形结构数据量极大（百万级节点）且查询频繁时，递归CTE可能成为性能瓶颈。
解法：考虑用闭包表（Closure Table）或路径枚举（Path Enumeration）方案来替代递归CTE。这两种方案以空间换时间，适合读多写少的场景。
六、总结
递归CTE是处理树形结构查询的标准写法，三个核心场景覆盖了大部分日常需求：
- 从根到叶：遍历组织架构、商品分类——
WHERE parent_id IS NULL往下找 - 从叶到根：面包屑导航、权限继承——
WHERE id = 当前节点往上找 - 带路径的子树：行政区域、BOM清单——用
CONCAT拼接完整路径
递归CTE的语法看起来有点吓人，但拆开看就是“锚点 + UNION ALL + 递归关联”三部分。写的时候记住三件事：锚点写对、索引建好、防环做好，剩下的就是多练。
小耶在手，SQL 不愁
还有什么想了解的，欢迎留言！小耶一定知无不言言无不尽……我们下次见~
