递归CTE实战：用SQL搞定树形结构查询，告别“写死”代码

发布时间：2026/7/17 2:00:50

大家好，我是小耶，写功课只是为了我踩过的坑，你们别再踩了！

你有没有写过这样的代码：查组织架构，先查顶层部门，再循环查子部门，每层写一个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选项来控制最大迭代次数。