企业官网建设流程全解析

递归SQL这几个字，我入行那会儿是有点绕道走的。当时的想法很直白：业务数据再复杂，不也就是一张表吗，我在程序里用for循环拼出树来，怎么都比在SQL里自己递归自己显得好懂。

真正被教育是在做商品分类系统的数据统计。类目表十几万行，最深能到八层，运营要求按一级类目汇总商品数，而且必须算上子孙类目。我用Java一层层getSubList()去查，数据库被打了几百上千次，一个接口从几十毫秒干到几秒钟，线上用户的反应是“页面卡得没法看”。换成一个递归CTE之后，接口回到170ms。从那以后我才明白，层级结构查询这件事，SQL本身就有正解，只是很多人没有认真用过。

这篇文章就围绕递归SQL写实战术。包含递归CTE的语法拆解、组织架构向下展开、向上反查汇报链、树形分类聚合下钻、循环引用挡不住怎么办，以及什么时候不该用递归。适合正在被parent_id表折磨的读者，也适合想系统掌握层级结构查询的中级SQL开发者。

1. 层级结构查询的痛点：为什么普通SQL搞不定一棵树

1.1 一张表里的父子关系，拆开了看很麻烦

先看最常见的模型，一张表存自身关联：

CREATE TABLE category ( id INT PRIMARY KEY, name VARCHAR(50) NOT NULL, parent_id INT NULL );

parent_id指向自己这张表的主键id，顶层节点的parent_id是NULL。这种设计简单、好维护，是组织架构、商品类目、菜单权限、BOM物料清单里用得最多的结构。但它最大的问题就在“查询”上。

你想知道“数码产品”下面直属子类目有多少，一条WHERE parent_id = xxx就能搞定。可你要是想知道“数码产品”下面所有子孙类目的商品总量呢？普通SQL写不了“我不知道到底有多少层”这种条件。你可以用LEFT JOIN多级自连接，三重、四重、五重……业务上出现一个八层深的树，你总不能真的JOIN八次吧。

自连接是一种硬编码层级的写法，它对层数有先验假设，一旦业务层级变化，SQL就要跟着改。我在实际项目里见过连续十多个LEFT JOIN的自关联查询，能跑，但维护它的人每次都要头大半天。递归CTE解决的就是“层数未知、动态遍历”的问题。

1.2 递归CTE的核心思路：给SQL一个“自我生长”的口子

递归CTE全名是Recursive Common Table Expression，它围绕一个结果集做反复迭代：先跑一个初始查询，拿到第一批数据，然后反复用这批数据去JOIN原表，把新结果再并进来，直到某一次迭代没有产生新数据为止。

我习惯用一个生活类比来解释：把一张叶子放进水池，叶子扩散出涟漪，涟漪继续往外扩散，每次扩散都基于上一圈的位置，直到碰到岸边停止。递归CTE里的“锚点成员”是最开始那圈涟漪，“递归成员”是每一圈往外推的逻辑，而“碰到岸边”就是终止条件。

这个东西不是某个数据库的独门技巧，MySQL 8.0、SQL Server 2005+、PostgreSQL、Oracle、SQLite都支持，只是语法表述有些差异。下面我以MySQL和SQL Server为主讲，其他数据库的差异点最后单独列一张表。

2. 递归CTE的三个核心构件：锚点、递归成员、UNION ALL

2.1 一个可以立刻复用的通用模板

绝大多数层级结构查询有一个相同的骨架，先把模板摆出来：

WITH RECURSIVE tree AS ( -- 锚点成员：第一次查出的初始数据 SELECT id, name, parent_id, 1 AS depth FROM category WHERE parent_id IS NULL UNION ALL -- 递归成员：基于上一次结果，向下找下一层 SELECT c.id, c.name, c.parent_id, t.depth + 1 FROM category c INNER JOIN tree t ON c.parent_id = t.id ) SELECT id, name, parent_id, depth FROM tree ORDER BY depth, id;

这段SQL在MySQL和PostgreSQL里可以直接跑。SQL Server的写法稍有不同，它不写RECURSIVE关键字：

WITH tree AS ( SELECT id, name, parent_id, 1 AS depth FROM category WHERE parent_id IS NULL UNION ALL SELECT c.id, c.name, c.parent_id, t.depth + 1 FROM category c INNER JOIN tree t ON c.parent_id = t.id ) SELECT id, name, parent_id, depth FROM tree ORDER BY depth, id;

执行过程是这样的：第一遍先取所有顶层节点，得到第一批结果；第二遍拿这批结果的id去匹配category.parent_id，找到所有二级节点，depth变成2；第三遍继续基于二级节点的id去找三级节点。以此类推，直到某一轮JOIN匹配不到任何子节点，整个CTE结束。

2.2 为什么递归成员必须用UNION ALL而不是UNION

这个坑比很多人想象的重要。UNION自带去重，看起来去重挺好，但在递归CTE里它会导致非常隐蔽的错误。

递归的每一轮迭代本身可能产生重复数据。比如一个节点通过不同路径都能到达，UNION去重后会把其中一条路径截断，下一轮递归失去这个节点，结果树就少了分叉。更重要的是，UNION在每次迭代时都要做一次去重，当递归层数多、每层数据量大时，性能下降非常明显。

官方文档也明确要求递归成员使用UNION ALL。我在代码review时看到有人把UNION ALL改成UNION，问原因说是“防重复”，我一般会拦下来——防重复应该在业务上去排查数据为什么有多条路径，而不是在递归语义上做文章。

2.3 递归成员里不能放的查询子句

新手最容易在递归成员里加ORDER BY、GROUP BY、LIMIT、DISTINCT这类东西。MySQL和PostgreSQL的递归成员不允许使用聚合函数、窗口函数、GROUP BY、ORDER BY、LIMIT等。原因不复杂：递归CTE是逐层迭代的结果集，每层需要被下一轮继续JOIN，如果某一层被聚合或排序截断，迭代的语义就被破坏了。

需要排序或分页，可以在CTE外层做，而不是递归成员里做。我见过一个报表查询，把ORDER BY放在递归成员里，MySQL直接报错，报错信息可能不够友好，但改到外层之后一切正常。

3. 实战一：组织架构向下展开，一次拿到所有下属与层级深度

3.1 先造一张员工表和测试数据

组织架构是最典型的层级结构业务。我造一张简化版员工表：

CREATE TABLE employee ( id INT PRIMARY KEY, name VARCHAR(50) NOT NULL, manager_id INT NULL ); INSERT INTO employee (id, name, manager_id) VALUES (1, '总经理', NULL), (2, '技术总监', 1), (3, '产品总监', 1), (4, '后端组长', 2), (5, '前端组长', 2), (6, '后端工程师A', 4), (7, '后端工程师B', 4), (8, '前端工程师A', 5), (9, '产品经理A', 3), (10, '运营专员A', NULL);

manager_id指向员工id，NULL表示没有上级。这个结构里既有三层、四层链，也有一个独立顶层节点，适合测试。

3.2 从总经理开始，向下递归找所有下属

需求是“给我总经理下面所有人的名单，并标出每个人在公司里的层级”。

WITH RECURSIVE emp_tree AS ( SELECT id, name, manager_id, 1 AS depth FROM employee WHERE manager_id IS NULL UNION ALL SELECT e.id, e.name, e.manager_id, t.depth + 1 FROM employee e INNER JOIN emp_tree t ON e.manager_id = t.id ) SELECT id, name, manager_id, depth FROM emp_tree ORDER BY depth, id;

跑出来的结果是：总经理depth为1，技术总监和产品总监depth为2，后端组长和前端组长depth为3，后端工程师A/B、前端工程师A、产品经理Adepth为4。

实际业务里，你往往不是从顶层查，而是从某个节点向下查。比如新来的后端组长想看自己团队有哪些人，把锚点从WHERE manager_id IS NULL改成WHERE id = 4即可，后面递归逻辑完全不用动。

3.3 在递归里输出完整组织路径

只输出depth还不够直观。我通常会在递归里拼一个路径字段，把ID路径或名称路径带出来，方便页面直接展示“总经理 / 技术总监 / 后端组长 / 后端工程师A”。

WITH RECURSIVE emp_tree AS ( SELECT id, name, manager_id, 1 AS depth, CAST(name AS CHAR(1000)) AS path FROM employee WHERE manager_id IS NULL UNION ALL SELECT e.id, e.name, e.manager_id, t.depth + 1, CONCAT(t.path, ' / ', e.name) FROM employee e INNER JOIN emp_tree t ON e.manager_id = t.id ) SELECT id, name, depth, path FROM emp_tree ORDER BY path;

ORDER BY path是按字符串排序的，细看会发现“后端组长”会排在“技术总监”后面，因为汉字编码排序不是我们日常字典序。如果需要保持树的自然顺序，最好额外维护一个sort_order字段，或者在应用层再处理。

3.4 路径字段隐藏的类型截断问题

这个坑我踩过一次，而且报错还特别不明显。

MySQL递归CTE里，列的类型由锚点成员决定。递归成员里如果我直接写CONCAT(t.path, ' / ', e.name)，MySQL会先看锚点里path字段的类型。假如我在锚点里写的是CAST(name AS CHAR(50))，路径一长，超过50个字符，MySQL不是报错，而是直接截断结果，你看到路径后半段莫名其妙消失，排查半天才发现是长度不够。

我现在的习惯是：锚点里字符串路径统一用CAST(... AS CHAR(1000))或足够长的VARCHAR。SQL Server里则用CONVERT(VARCHAR(1000), ...)，Oracle用CAST(... AS VARCHAR2(1000))。宁可多给一些长度，不要等线上数据量变大后再去改字段类型。

4. 实战二：向上反查汇报链，从任意节点追到顶层

4.1 反查上级链路的写法

业务上还有一类需求很常见：我从员工工号出发，想知道他的完整汇报线，也就是从底层一路追到CEO。

比如我想知道员工id=6这条汇报线，怎么写？锚点从id=6这个员工开始，递归成员里去JOIN员工表的manager_id，把当前节点的上级找出来，再反复向上。

WITH RECURSIVE up_chain AS ( SELECT id, name, manager_id, 1 AS depth FROM employee WHERE id = 6 UNION ALL SELECT e.id, e.name, e.manager_id, u.depth + 1 FROM employee e INNER JOIN up_chain u ON e.id = u.manager_id ) SELECT id, name, manager_id, depth FROM up_chain ORDER BY depth DESC;

注意这里JOIN的方向和向下递归是反的。向下递归用c.parent_id = t.id，向上递归用e.id = u.manager_id，意思是“当前递归结果的manager_id是谁，我就把谁查出来”。

跑出来的结果里，depth=1是id=6本人，depth=4是总经理。ORDER BY depth DESC后，展示顺序就变成总经理到本人，正好是领导链路。

4.2 把汇报链做成可读的职级链路

如果想把汇报链变成“总经理 -> 技术总监 -> 后端组长 -> 后端工程师A”，技巧和向下递归一样，拼接路径字段，但需要先倒序再拼接。

也不算复杂，一个常用做法是在CTE里记录路径，然后外层用函数把字符串反转或在应用层处理。不同数据库函数不同，MySQL里没有直接的REVERSE按分隔符切片函数，我一般直接查出来后交给前端展示。递归SQL适合把结构算出来，不适合做花哨的字符串排版。

4.3 递归深度的保护机制和调参

向上递归和向下递归都有一个危险：如果表里存在循环数据，比如manager_id指回自己，或者A上级是B、B上级是A，那么递归永远停不下来。

SQL Server默认会保护你，最大递归次数默认100，超出后报错“Recursive Common Table Expression did not terminate”。MySQL 8.0也有保护参数cte_max_recursion_depth，默认1000。如果查询确实需要超过默认层数，可以在会话级别调整：

SET SESSION cte_max_recursion_depth = 5000;

SQL Server则是在查询末尾加OPTION：

SELECT * FROM up_chain OPTION (MAXRECURSION 500);

调参数前先想清楚一件事：正常的组织架构最多也就十几层，你如果真的需要调到几千层，大概率不是深度不够，而是数据有环。所以我的经验是先跑一次带路径检测的递归，确认没有循环，再决定要不要调大参数。

5. 实战三：树形分类的聚合下钻，统计整棵子树的数据量

5.1 需求拆解：类目树下的商品统计

这个场景我开篇提到过，电商后台几乎必做。类目表是树形，商品表挂在某个具体类目下，运营想看“一级类目下总共有多少商品”，这个总数必须包含所有子孙类目。

直接用GROUP BY category_id会漏掉子类目的商品。常规做法是先在程序里逐层展开，再统计，接口会非常慢。用递归CTE，直接把每个节点映射到它的根类目，然后一次JOIN商品表完成聚合。

5.2 根节点映射法：先展开归属关系，再JOIN聚合

核心思路是：递归CTE生成一张“节点到根节点”的映射表，然后商品表按这张映射表归到最上层类目。

WITH RECURSIVE cat_map AS ( SELECT id, id AS root_id, parent_id FROM category WHERE parent_id IS NULL UNION ALL SELECT c.id, t.root_id, c.parent_id FROM category c INNER JOIN cat_map t ON c.parent_id = t.id ) SELECT c.id AS root_category_id, c.name AS root_category_name, COUNT(DISTINCT p.id) AS product_cnt FROM cat_map t INNER JOIN category c ON t.root_id = c.id LEFT JOIN product p ON p.category_id = t.id GROUP BY c.id, c.name ORDER BY c.id;

这里cat_map每一行都代表“节点id归属于哪个根节点”。递归过程中，root_id保持不变，从根节点逐层向下传播。最后JOIN商品表时，用的是t.id而不是t.root_id，这样每个类目下的商品都能正确挂到自己的根头上。

如果只要统计某个子树，比如“数码产品”下面所有子孙类目的商品数，锚点改成WHERE id = 数码产品的id，再把root_id改成这个子树的id，逻辑一样。

5.3 聚合统计最容易遇到的COUNT翻倍问题

写这个查询最怕的情况是：一个商品因为数据脏被重复统计。常见脏数据是类目表里某个节点同时有两个父节点，递归CTE会把这个节点沿两条路径各展开一次，商品表被JOIN两次，COUNT就翻倍了。

我在实际数据中排查这类问题时，先跑一遍不带聚合的映射表，看有没有一个节点出现在多行里：

SELECT id, COUNT(*) AS cnt FROM cat_map GROUP BY id HAVING COUNT(*) > 1;

如果有，说明类目表的父子关系存在循环或有多个父节点。修复脏数据比在COUNT上打补丁更靠谱。代码里用COUNT(DISTINCT p.id)也算一道防线，但根本问题还是树结构本身要干净。

6. 循环引用和深树翻车点：如何用路径字段拦截无限递归

6.1 循环引用的现场还原

假设类目表里有一条脏数据：id=100的parent_id=200，id=200的parent_id=100。用普通递归CTE查下去，结果集会在100和200之间永远往返，直到触发数据库保护参数报错。

这不是理论问题。有一次测试环境数据量才两千行，我跑一个向下递归查询，MySQL直接报错超出最大递归深度。当时第一反应是“两千行怎么会有这么深的树”，后来用路径字段排查，果然发现一条自引用数据。排错思路比结论更值得分享。

6.2 用路径字段既展示路径，又拦截循环

在递归里维护path字段，除了展示，还能拦截循环。核心思路是：每次准备进入下一个节点时，看这个节点的id是否已经在当前路径中出现过，如果出现过，不再继续往下走。

WITH RECURSIVE category_loop AS ( SELECT id, parent_id, CAST(id AS CHAR(1000)) AS path FROM category WHERE parent_id IS NULL UNION ALL SELECT c.id, c.parent_id, CONCAT(t.path, ',', c.id) FROM category c INNER JOIN category_loop t ON c.parent_id = t.id WHERE LOCATE(CONCAT(',', c.id, ','), CONCAT(',', t.path, ',')) = 0 ) SELECT id, parent_id, path FROM category_loop;

这里把path字符串设计成“被逗号包裹”的格式，比如当前路径是“,1,3,5,”，要判断下一个节点id=5是否出现过，就查“ ,5,”在“ ,1,3,5,”中是否存在。这种写法的目的是避免误判，比如id=1和id=12这类包含关系，如果不用逗号包裹，判断“1是否出现过”时会错误地认为12里也有1。

LOCATE在MySQL里是查找子串位置，对应PostgreSQL是POSITION，SQL Server是CHARINDEX。逻辑都一样，注意翻译成对应方言。

6.3 数据库保护参数怎么设才合适

保护参数不只是给查询兜底，它也是排查循环引用的第一手线索。SQL Server默认MAXRECURSION是100，如果只是普通深树，100层多半够用；如果超过100层，报错信息本身就说明数据里大概率有环。

MySQL 8.0默认cte_max_recursion_depth是1000，同样够大部分业务用。需要注意的是，这个参数是会话级变量，连接断开后恢复默认值。我一般在排查循环问题时先不调大，等确认数据没问题后再设置一个合理的上限，避免程序里出现某些恶意循环拖垮数据库。

PostgreSQL没有独立的递归层数限制提示，但它也不是真的无限。遇到递归不终止的情况，我习惯在递归成员里手动加上WHERE t.depth < 50之类的硬限制，给查询一个明确保险丝。

6.4 一次真实排查：从报错到定位脏数据的完整链路

那次两千行数据报超出最大递归深度，我完整的排查过程是这样的：

第一步，确认异常是不是数据引起的。直接把WHERE条件里的锚点从根节点换成单条已知数据，仍然报错，说明问题不在某个特殊根节点，而是全表存在环。

第二步，把递归CTE加上path字段，并且去掉循环拦截条件，故意让它继续无限递归，但通过LIMIT 1000限制结果集大小，快速看path长什么样。很快发现有一条path在反复出现相同id序列：“100,200,100,200,100,200”。

第三步，根据这个路径查这两条原始数据，发现id=200的parent_id指向100，id=100的parent_id也指向200，确定是循环引用。

第四步，清理其中一条脏数据后，再用原始递归CTE跑，深度只剩7层，问题消失。整个过程不到二十分钟，核心就是通过path字段观察“是否有重复id出现在路径中”。

7. 递归不是唯一方案：物化路径、嵌套集与我实际的选择标准

7.1 递归CTE的性能瓶颈先看清楚

递归CTE虽然写起来痛快，但它不是万能的。性能瓶颈主要来自两点：一是每一层递归都要单独扫描一次表，即使有索引，深树的查询次数也会累计到不可忽视的量级；二是递归结果集被反复引用，当数据量涨到百万级，MySQL等数据库的递归性能会明显下降。

如果你的同步接口只是几百毫秒的差距，递归CTE足够用。但如果你的树形数据是核心链路里的高频查询，比如前台每个页面都要展示分类树，那就要考虑用空间换时间的方案。

7.2 物化路径模型：用冗余字符串换查询速度

物化路径的核心是把树的完整路径直接冗余在一张表的字段里，比如每个节点存储1/3/5/8这样的path。查询某个节点下所有后代，用前缀LIKE一次搞定：

SELECT * FROM category WHERE path LIKE '1/3/%';

物化路径的查询性能非常好，因为可以用前缀索引，而且不需要递归。代价是插入节点时要生成完整path，移动节点时要更新这个节点下所有子节点的path前缀。这个模型特别适合“树深、数据量大、读多写少”的业务，比如前台商品类目的搜索与展示。

7.3 嵌套集模型：左右值区间查询，但维护成本高

嵌套集模型是在表里加lft和rgt两列，用区间包含关系表示树的包含关系。查询一个子树时，只要找所有lft落在父节点lft和rgt之间的节点即可，一次扫描，不需要递归。

这个模型最大的问题在于插入、删除、移动节点需要重新计算一堆节点的左右值，维护成本很高。我在生产环境里见过两次尝试用嵌套集的团队，最后都因为类目频繁调整而放弃了。它不是不好，而是要非常确认树的结构几乎不发生变动才能用。

7.4 不同数据库对递归CTE的支持差异一览

这个表只是给大家一个快速参考。实际开发中我建议先确认你用的数据库版本，避免写出不兼容的递归语法。

7.5 我实际选型时是怎么拍板的

我一般用三个条件来选方案。第一看树的深度和规模：五层以内、十万行以下，闭眼用递归CTE，代码可读性最好，后续维护也最轻松。第二看查询频率和写频率：如果树形数据的查询是高频核心路径，而更新很少，物化路径是首选；如果节点本身经常移动，递归CTE反而更灵活，因为改一个parent_id就算完事，物化路径和嵌套集都要动一堆子节点。第三看团队维护成本：一个普通后端能把递归CTE写明白，物化路径也不难理解，嵌套集则需要时间和经验沉淀，轻易不要上。

举个例子，我做过的电商后台里，前台店铺类目树要求响应速度，我用物化路径加前缀索引，压到了几毫秒；但后台运营要随时拖拽调整类目层级，调整时我用递归CTE重新生成所有受影响节点的path，再批量更新。两个方案不是互斥的，同一个树形结构可以同时用两种手段服务不同场景。

最后聊一下我现在的习惯。遇到层级结构查询需求，我第一反应永远是递归CTE，先跑出一个能用的结果，确认业务逻辑和数据没问题。等数据量测试真的撑不住，再考虑物化路径这种折中方案。大多数业务场景其实到不了“数据量大到递归撑不住”的程度，反而是循环数据、路径字段截断、方言差异这些细节更容易让项目踩坑。把这些细节记牢，你的递归SQL就能从“会写”变成“写得稳”。