最近技术社群里又开始刷屏式地转发各种连接报错我在多个数据库交流群里看到不少人在问一个问题递归CTE和HAVING到底能不能一起用为什么会“连接不上”这个问题看起来很奇怪因为递归CTE是SQL标准里的高级功能HAVING是聚合过滤的必备子句两者似乎井水不犯河水。但实际一写才发现这俩一旦凑到一起报错、返回错数据、性能翻车的情况比比皆是。我最初遇到这个问题是做一个组织架构的层级报表。需求本身很简单把公司所有部门按树形结构拉出来然后只保留那些“直接下属部门数量超过3个”的节点。我第一反应就是用递归CTE遍历部门树然后HAVING过滤。结果跑了半天要么报语法错误要么查出结果和预期完全不符。后来花了一整天排查才把递归CTE和HAVING的执行机制彻底弄明白。这篇东西没有基础概念讲解直接讲实战。我会把递归CTE里HAVING为什么会失效、什么时候能生效、什么时候即使能生效也不该用全部拆开讲清楚。文章涉及的所有SQL我默认以PostgreSQL 13为运行环境MySQL 8、SQL Server、Oracle的差异会在必要时单独标注。1. 这俩看着像两条平行线但合在一起写就翻车1.1 单独看都不陌生合起来却经常写错递归CTERecursive Common Table Expression核心功能是遍历有层级关系的数据比较典型的使用场景是组织架构、商品分类树、BOM物料清单、评论楼中楼。HAVING子句解决的核心问题是“对分组聚合后的结果做过滤”典型场景是统计每个分类的商品数量后只保留数量超过指定阈值的分类。单独用其中一个绝大多数开发都不会出问题。但把递归CTE和HAVING组合起来写的时候新手和老手都会踩同一个坑把HAVING直接写进递归成员递归部分里。比如下面这段写法我相信不少人都试过WITH RECURSIVE dept_tree AS ( SELECT id, parent_id, name, 1 AS depth FROM department WHERE parent_id IS NULL UNION ALL SELECT d.id, d.parent_id, d.name, dt.depth 1 FROM department d JOIN dept_tree dt ON d.parent_id dt.id HAVING COUNT(d.id) 3 -- 这行几乎一定会报错 ) SELECT * FROM dept_tree;我在几个数据库上实测过这个写法PostgreSQL会直接报出语法错误MySQL 8也会报错SQL Server会提示“HAVING在该上下文中无效”。这不是某个数据库的实现缺陷而是SQL标准里固有的规则。要理解为什么得先搞清楚递归CTE执行时到底发生了什么。1.2 什么样的业务场景会让“递归 聚合过滤”同时出现先别急着关心语法能不能过先确认一个更根本的问题业务上到底存不存在“非要用递归CTE去聚合过滤”的场景。从我实际做过和见过的项目来看这类需求比想象中常见得多。典型的两类第一类是层级路由过滤。比如商品类目表是树形结构每个类目下面挂了几百个商品。需求是“找出所有‘挂载商品数大于500’的二、三级类目路径”。这个场景需要在展开整棵类目树的过程中对每个类目子节点统计商品数再把这个数量作为决定是否继续深入遍历的条件。第二类是树形节点指标裁剪。组织架构、权限树、设备拓扑经常会遇到“找出一棵树上所有满足某聚合条件的子树路径”比如“日均访问量超过一万的二级部门分支”。这里既要递归下钻拿到完整路径又要在每一层做聚合判断。聚合对象不是全表分组而是递归过程中每个节点及其子孙节点的集合。这两类需求网上能找到的默认答案大多是用“临时表 循环”或“程序里递归”去做。在SQL里直接硬写递归CTE配合HAVING的案例确实少。但少不代表不能做关键是要搞清楚正确的组合顺序。2. 递归CTE的执行真相为什么不能把HAVING塞进递归成员里2.1 递归CTE分四步走每一步都有自己的规则很多人的误区在于把递归CTE当成一段“自动循环执行的普通SQL”。实际上数据库处理递归CTE的方式和普通子查询完全不同。以PostgreSQL为例一个标准的递归CTE执行过程是这样的第一步执行锚点成员ANCHOR MEMBER。也就是UNION ALL之前不在FROM里引用CTE自身的那个查询。这个查询产出的行集称为“工作表”working table这是递归的第一轮结果。第二步执行递归成员RECURSIVE MEMBER。这个成员在FROM里引用了CTE自身。数据库会把当前的工作表当作输入和递归成员里的其他表做JOIN产出一批新行这批新行构成“下一轮的工作表”。第三步数据库把新产出的行UNION ALL进最终结果集然后重复第二步。只要每一轮还能产出新行循环就继续。第四步当某次执行递归成员后没有产出任何新行递归终止数据库返回累积的完整结果集。这四步每一步之间是独立执行的递归成员里写的东西每一轮都会被重新执行一次。这里就引出一个关键结论递归成员里其实是不允许出现聚合函数和分组子句的。在PostgreSQL里递归CTE的递归成员在语义上被限制成“非聚合查询”因为它每一轮要处理的是“上一轮的结果集 当前表的连接”这种逐行迭代的处理模型天然排斥需要全量扫描才能完成的聚合操作。2.2 在递归成员里写GROUP BY/HAVING会怎么样从语法层面说SQL标准对递归成员的要求简单粗暴递归成员的FROM子句必须且只能引用一次CTE自身而且这里的引用不允许出现在子查询、聚合函数、GROUP BY或HAVING的内部或作用域里。PostgreSQL文档里专门有一条递归查询的递归成员不允许包含聚合函数不允许使用GROUP BYORDER BY也只能出现在最外层。这条规则的本质原因是递归成员在每一轮被调用时工作表中的行数、内容都在变化。如果允许在递归成员里做GROUP BY和聚合数据库无法确定“聚合的范围”到底是当前这一轮的行还是历史所有轮次的行。语义上存在根本歧义各数据库厂商干脆一起禁止。所以哪怕你连GROUP BY都不写只写一个HAVING同样会报错。因为HAVING本身就是和GROUP BY绑定的聚合过滤子句。2.3 聚合出现在哪一层决定你能过滤什么既然递归成员里不能写聚合那递归CTE和HAVING到底还能不能在同一条SQL里用当然能。关键在于把HAVING放在什么位置。我总结了一个简单粗暴的判断原则聚合的层级就是过滤的层级。如果你想过滤的是“递归CTE最终输出结果”的某个分组那就把聚合和HAVING放在最外层的查询里。比如先递归遍历整个组织架构然后在外层按某个字段GROUP BY再用HAVING过滤。这种情况写起来没有任何障碍因为最外层查询是普通查询不涉及递归限制。如果你想过滤的是“每个递归路径内部的子集”比如遍历到某个节点时要判断它名下所有子节点的数量是否达到阈值那就要把这个判断改写成“非聚合形式”。常见手法有两个一个是把聚合判断放到递归成员之外用EXISTS、NOT EXISTS、JOIN子查询去实现另一个是先把聚合结果通过窗口函数比如COUNT(*) OVER()计算出来再在外层过滤。具体怎么写下一章展开。还有个容易忽略的细节即使最外层可以使用GROUP BY和HAVING也不建议让递归CTE把所有层级的数据全部展开后再做聚合。因为递归CTE天然会把所有中间行都物化到临时工作表中行数会随着层级指数级膨胀。最外层再做一次GROUP BY相当于把膨胀后的数据又扫一遍。这种写法在数据量小的时候没问题一旦节点数上万、层级超过五六层性能就会很难看。3. 实战案例先递归拉全量再在外层剪枝怎么写才不踩坑3.1 数据模型与目标一个真实的部门树场景为了把问题讲透我用一个我实际处理过的部门数据模型来做示例。表结构如下CREATE TABLE department ( id INT PRIMARY KEY, parent_id INT REFERENCES department(id), name VARCHAR(100) NOT NULL, team_size INT NOT NULL DEFAULT 0, created_at DATE NOT NULL ); INSERT INTO department (id, parent_id, name, team_size, created_at) VALUES (1, NULL, 总公司, 10, 2020-01-01), (2, 1, 技术中心, 30, 2020-02-01), (3, 1, 市场中心, 25, 2020-03-01), (4, 2, 后端组, 15, 2020-04-01), (5, 2, 前端组, 12, 2020-05-01), (6, 4, 支付组, 8, 2020-06-01), (7, 4, 账号组, 7, 2020-06-15), (8, 5, 中台组, 9, 2020-07-01), (9, 3, 品牌组, 20, 2020-08-01);需求是找出所有“团队总人数超过50人”的顶级部门路径。注意这里的“顶级”不是根节点而是指能作为一条路径起点的顶层节点。翻译成人话先把整棵树展开成“根路径字符串 每行部门信息”然后按根路径分组统计每个根路径下的总人数过滤出超过50的路径。3.2 第一版递归拉全量最外层直接HAVING这个版本最直观结构清晰也是大多数人第一次写出来的版本WITH RECURSIVE dept_tree AS ( SELECT id, parent_id, name, team_size, id::TEXT AS root_path, 1 AS depth FROM department WHERE parent_id IS NULL UNION ALL SELECT d.id, d.parent_id, d.name, d.team_size, dt.root_path || - || d.id::TEXT AS root_path, dt.depth 1 FROM department d JOIN dept_tree dt ON d.parent_id dt.id ), path_stats AS ( SELECT root_path, SUM(team_size) AS total_team_size FROM dept_tree GROUP BY root_path ) SELECT ps.*, dt.name AS root_dept_name FROM path_stats ps JOIN ( SELECT DISTINCT ON (root_path) root_path, id, name FROM dept_tree ORDER BY root_path, depth ) dt ON ps.root_path dt.root_path WHERE ps.total_team_size 50 HAVING COUNT(ps.root_path) 0; -- 注意这一行故意留了个问题看到最后那个HAVING没有这是我故意写的。实际运行这段代码会直接报错因为WHERE和HAVING同时出现在同一个SELECT层里语义冲突。很多人第一次这么写就是在这里翻车的HAVING要么不放要么放在GROUP BY之后不能和WHERE混在同一层。而且这里GROUP BY的对象是root_pathCOUNT(ps.root_path)算的是“每个路径的组内行数”和“总人数超过50”这个需求没有关系。这个版本的另一个问题在性能上递归CTE先展开了一棵全树然后才做GROUP BY聚合。如果只是组织架构这种几百行的小表确实不算什么。但换成商品分类树每个分类几万商品根节点又特别多时展开出来的中间结果表会有几十万行整个查询会变得相当沉重。3.3 第二版用JOIN子查询替代递归内的聚合判断来看正确的标准写法。这个版本的核心思路是不在递归成员里做聚合而是用JOIN把一个“预先按路径分组统计好的子查询”挂到最外层再做HAVING过滤WITH RECURSIVE dept_tree AS ( SELECT id, parent_id, name, team_size, id::TEXT AS root_path, 1 AS depth FROM department WHERE parent_id IS NULL UNION ALL SELECT d.id, d.parent_id, d.name, d.team_size, dt.root_path || - || d.id::TEXT AS root_path, dt.depth 1 FROM department d JOIN dept_tree dt ON d.parent_id dt.id ) SELECT root_path, MIN(name) AS root_dept_name, -- 根节点名称路径的起点 SUM(team_size) AS total_team_size FROM dept_tree GROUP BY root_path HAVING SUM(team_size) 50;这个写法就能正确执行。它的核心逻辑递归CTE负责展开树产出“每一行 一个节点 它所属的根路径字符串”最外层查询按根路径分组SUM(team_size)汇总该路径下所有节点的团队人数HAVING把总人数大于50的路径筛掉。这里有两个操作细节值得说一下第一个细节root_path字段的设计。我用的是“根节点ID 箭头 子节点ID”拼接而成的字符串路径。这种方法的好处是递归展开时只需要拼接字符串不需要回表查询路径。坏处是路径字符串会随层级加深变长字符串拼接开销随深度增加。更规范的做法是用数组或层级物化路径比如PostgreSQL里直接用ltree类型或者维护一个int[]类型的ancestors字段。但字符串拼接最适合做示例因为它不依赖特定扩展所有数据库都能跑。第二个细节MIN(name)的使用。为什么要用MIN(name)而不是直接取dt.name因为按root_path分组后组内只有root_path可以确定是一条路径的起点想要拿到根节点名称可以用MIN聚合。也可以在最外层JOIN原表用条件“dept_tree.id 路径字符串中的第一个ID”来取。MIN写法会比较简洁代价是语义不够直观。如果路径字符串拼接格式是固定的我更建议在递归CTE里额外加一列root_name这样最外层直接GROUP BY root_path, root_name就行避免MIN这种让人疑惑的写法。3.4 第三版递归过程中“预聚合”减少中间行数的进阶思路上面的写法能解决问题但性能还有优化空间。当树特别深、节点特别多时把整棵树展开再聚合中间结果集太大内存和临时文件都会有压力。这里有一个不容易想到的做法既然每个节点在递归展开时已经知道了自己的路径字符串就可以在递归成员里顺便计算“当前路径下所有节点的累计人数”。但问题来了递归成员里不能写聚合函数。怎么办答案是用窗口函数。窗口函数和聚合函数不同它不算“聚合”它是“逐行计算但能看到整个分组”PostgreSQL、Oracle、SQL Server都支持MySQL 8也支持。在递归成员里写窗口函数语法上完全合法WITH RECURSIVE dept_tree AS ( SELECT id, parent_id, name, team_size, id::TEXT AS root_path, team_size AS path_team_size, 1 AS depth FROM department WHERE parent_id IS NULL UNION ALL SELECT d.id, d.parent_id, d.name, d.team_size, dt.root_path || - || d.id::TEXT AS root_path, dt.path_team_size d.team_size AS path_team_size, dt.depth 1 FROM department d JOIN dept_tree dt ON d.parent_id dt.id ) SELECT root_path, MIN(name) AS root_dept_name, MAX(path_team_size) AS total_team_size FROM dept_tree GROUP BY root_path HAVING MAX(path_team_size) 50;这套写法我把SUM(team_size)换成了路径累计值path_team_size。递归过程中每往下一层就把当前节点的team_size往上累加。因为路径字符串root_path本身就是从根到当前节点的一段连续路径这个累计值等价于“当前路径下的所有节点人数之和”。最外层按root_path分组后同一路径下的所有行里路径最深的那一行拥有最大的path_team_size所以用MAX(path_team_size)就能拿到整条路径的总人数。这个进阶写法的性能收益很明显把“全路径展开后再次全量扫描聚合”退化成了“逐行递推累加”归并的工作量大幅下降。代价是递归中间行里多了path_team_size这一列每行要多维护一个整数。这在绝大多数场景下是划算的。不过要注意这个写法有个前提team_size是正数累计值不会出现“叶子节点反而比根节点小”的反常情况。如果业务上可能出现负数指标MAX取最大值就会失效这时候必须回到SUM写法。4. 踩坑实录递归结果里HAVING“不起作用”的完整排查链路4.1 现象明明加了HAVING为什么结果还是不对有个朋友在做商品类目树优化时遇到了一个特别诡异的问题。他的SQL大概长这样WITH RECURSIVE category_tree AS ( SELECT id, parent_id, name, product_count, id::TEXT AS path FROM category WHERE parent_id IS NULL UNION ALL SELECT c.id, c.parent_id, c.name, c.product_count, ct.path || - || c.id::TEXT FROM category c JOIN category_tree ct ON c.parent_id ct.id WHERE c.product_count 100 ) SELECT path, SUM(product_count) AS total_products FROM category_tree GROUP BY path HAVING SUM(product_count) 500;他反馈的问题是总有一些产品数明显不足500的路径出现在结果里。单独抽一条路径验证发现路径下所有节点的product_count加起来根本不超过500甚至有的只有一两百。我当时让他把SQL分段执行一步步排查。4.2 排除法定位问题不在HAVING而在递归成员的WHERE第一步去掉最外层的HAVING直接看递归CTE产出的原始数据WITH RECURSIVE category_tree AS (...同样的递归部分...) SELECT path, SUM(product_count) AS total_products FROM category_tree GROUP BY path;结果确实出现了大量product_count总和很小的路径。这个现象本身说明递归过程已经把“不想要的路径”保留下来了。问题出在哪仔细看递归成员里的这个条件WHERE c.product_count 100这句话看起来是在过滤“每个节点的产品数大于100才继续展开”但实际上它干了一件更隐蔽的事它把不满足条件的节点连同它的所有子节点一起从递归结果里剪掉了。在这棵树里如果一个中间节点的product_count不大于100数据库会直接放弃继续向下遍历这意味着它下面所有子节点也不会出现在结果集里。这其实不是HAVING的问题而是“过滤边界”的问题。如果某个中间节点的产品数本来就很小但它下面的某个子节点产品数巨大整个路径的总产品数其实很高结果因为这个中间节点的product_count不够下面那一大块子节点全被剪掉了。这个现象在递归CTE里非常典型**递归成员里的WHERE条件是“逐行裁剪”不是“分组裁剪”。**你以为写的是“只要当前节点满足条件就继续往下走”实际上写的是“只要当前节点不满足条件这条分支就断掉”。一大批本应保留的分支因此被剪掉另一批满足节点条件、但聚合后不达标的路径却被保留。排查到这里根因已经清楚了。HAVING一直没起作用不是因为HAVING写错了而是递归过程早就把“候选集”给改变了。最外层的HAVING只能过滤递归CTE已经产出的行无法还原已经被剪掉的子节点。4.3 正确姿势用EXISTS处理“每个节点的过滤”把聚合判断留给外层同类需求如果确实希望在递归过程中“提前剪枝”必须用EXISTS或IN子查询来改写让每一层的过滤条件基于“子树的整体情况”而不是“当前节点单行的字段值”。比如需求改为“只保留那些‘总产品数大于500’的类目路径”做法分成两步第一步递归CTE里不做任何WHERE裁剪先把整棵类目树完整展开。第二步用窗口函数或GROUP BY HAVING在外层判断哪些路径总产品数达到500然后只输出这些路径。如果担心完整展开的中间结果集太大可以用EXISTS子查询把“剪枝”提纯成“判断子树是否值得继续展开”WITH RECURSIVE category_tree AS ( SELECT id, parent_id, name, product_count, id::TEXT AS path FROM category WHERE parent_id IS NULL UNION ALL SELECT c.id, c.parent_id, c.name, c.product_count, ct.path || - || c.id::TEXT FROM category c JOIN category_tree ct ON c.parent_id ct.id WHERE EXISTS ( SELECT 1 FROM category sub WHERE sub.id c.id OR sub.parent_id c.id OR sub.parent_id IN ( SELECT id FROM category WHERE parent_id c.id ) ) )不过坦白讲这种EXISTS写法在递归里很容易把子查询写乱也不够通用。我个人的经验是如果中间结果集没有大到不可接受优先选择“完整展开 外层聚合过滤”的写法逻辑清晰调试方便。只有当树的规模实在太大展开全量行会内存溢出时再考虑业务预处理或程序侧递归。4.4 一个反直觉的实验同一段SQL在MySQL 8和PostgreSQL里的行为差异把同一个“错误”的递归CTE在递归成员里写GROUP BY和HAVING分别放到MySQL 8和PostgreSQL 14里跑会有完全不同的反馈数据库在递归成员里写HAVING的行为结果PostgreSQL解析阶段直接报语法错误快速失败容易定位MySQL 8部分场景能“容忍”HAVING但把它当普通过滤条件执行不报错但语义和预期严重不符SQL Server报错信息提示递归部分不允许聚合快速失败Oracle允许把HAVING放在递归成员里但要求必须有GROUP BY结果大概率不符合预期MySQL 8这个“能容忍”最坑人。它不是真的支持递归成员内聚合而是把HAVING当作一个可以直接跟着WHERE一起执行的普通过滤条件。比如你在递归成员里写HAVING COUNT(*) 3它直接按“当前行的某个字段值大于3”来过滤而不是先分组再过滤。结果自然就是错的而且非常难排查。这个坑我遇到过至少三次。每次都是同事把开发环境从PostgreSQL换成MySQL或者反过来然后SQL报错或结果不一致排查半天才发现是递归成员里写了不该写的聚合语句。所以这里有一个非常实用的检查习惯在递归CTE中递归成员里出现的GROUP BY、HAVING、聚合函数一律视为语法错误去怀疑。即使某个数据库允许它跑过去也要狠心改写成外层聚合或窗口函数方案。5. 性能、循环保护与替代方案别让递归CTE成为慢查询元凶5.1 递归深度与无限循环HAVING不能帮你刹住递归很多人以为给递归CTE加个HAVING就能控制递归轮数这是误解。HAVING是“过滤结果”的无法中断“已经发生的递归”。递归CTE真正需要关心的性能指标是“迭代轮数”和“每轮产生的行数”。数据库对递归CTE的保护机制非常简单粗暴要么有“最大递归深度”参数要么有“最大结果集行数”限制。PostgreSQL里有一个专门的参数SET recursive_cte_max_depth 100;MySQL 8也有类似的限制默认是1000层。SQL Server则是通过选项MAXRECURSIONOPTION (MAXRECURSION 50);这个限制一旦触顶数据库直接报错整条SQL失败。如果你的树本身超过限制要做的是重新审视这个树的结构是否合理而不是试图用HAVING来绕过。我处理过一个最典型的“无限递归”案例数据里存在循环引用也就是A的parent_id指向BB的parent_id又指向A。这种脏数据一旦存在递归CTE会永远循环下去。如果没有触发深度保护数据库可能先卡死。后来我们在所有会上递归的查询前面统一加了一层数据质量检测专门查循环引用一旦发现就报警。这个检测SQL本身也可以用递归CTE写但限制深度为树的最大可能深度比如100WITH RECURSIVE detect_loop AS ( SELECT id, parent_id, 1 AS depth, ARRAY[id] AS visited FROM department WHERE parent_id IS NOT NULL UNION ALL SELECT d.id, d.parent_id, dl.depth 1, dl.visited || d.id FROM department d JOIN detect_loop dl ON d.parent_id dl.id WHERE dl.depth 100 AND NOT (d.id ANY(dl.visited)) ) SELECT visited FROM detect_loop WHERE id ANY(visited) -- 如果某个id在visited数组里出现不止一次说明有环 LIMIT 10;这个检测方案的核心是维护一个“已访问节点ID数组”visited每次递归都判断当前节点是否已经在visited里。只要出现重复就说明存在循环。同样这个判断必须在递归成员里用NOT (d.id ANY(dl.visited))完成不能拖到外层再过滤否则递归根本停不下来。5.2 当递归深度和中间行数双双膨胀时考虑这三条替代路线递归CTE有一个所有数据库都绕不开的瓶颈中间结果集必须全部物化到临时存储。只要中间行数一多不管最终结果多小整个查询的内存和临时文件消耗都下不来。如果你在递归CTE上再叠加GROUP BY HAVING做聚合情况会更糟。当你发现递归CTE已经明显拖慢数据库时建议按以下顺序考虑替代方案第一个替代路线是“层级物化路径表”。如果树结构相对固定不经常变更可以提前给每行维护一个物化路径字段比如path为“/1/4/6”。查询某个节点的所有子孙直接LIKE /1/4/%就行。这种写法不需要递归查询速度飞快。代价是写入时要维护路径字段插入删除时需要对子树路径做批量更新。第二个替代路线是“程序侧递归”。把树的节点一次性加载到应用内存然后按level、childrenMap等结构存储程序里用栈或队列遍历。对几十万节点以内的树这种方法通常比SQL递归CTE快逻辑也更灵活。缺点是需要把数据从数据库拉出来网络I/O和内存占用必须可控。第三个替代路线是“临时表 循环”。在存储过程或脚本里先建临时表存根节点然后while循环里反复JOIN原表把下一层子节点插入临时表直到不再产生新行为止。这种方式虽然不如递归CTE优雅但有一个好处每一轮循环后都可以随时查看中间结果方便调试同时对“每轮循环中计算路径累计指标”这类需求来说怎么写都不会触发“递归成员不能聚合”的限制。我个人的切分原则是深度不超过10层、总节点数不超过10万的树放心用递归CTE 外层聚合超过这个量级优先考虑物化路径表或程序侧递归。不是递归CTE不好而是数据库的临时表物化机制决定了它在超大数据集上不占优势。5.3 聚合过滤时的索引与私有变量优化最后聊一个被很多人忽略的性能细节。当递归CTE最终结果需要做GROUP BY聚合时有没有索引差别非常大。递归CTE中最常见的性能瓶颈不是递归本身而是JOIN主表和递归结果时的连接操作。上面所有例子里递归成员都写了这样的JOINJOIN dept_tree dt ON d.parent_id dt.id这里的驱动顺序是先用dept_tree递归结果里的一行作为输入去department表里找parent_id匹配的行。如果department表在parent_id上没有索引数据库每一次都要做全表扫描。递归每展开一层就要把department整表扫一遍。啊这就是递归CTE里面最想吐槽的一种“双重全表扫描”问题。解决办法就是在parent_id上建索引CREATE INDEX idx_department_parent_id ON department(parent_id);这个索引直接影响递归每一轮的连接效率属于必须建的索引不是可选项。我见过一个案例建索引前递归CTE跑12秒建完瞬间降到200毫秒。差别之大令人咋舌。在递归CTE之前先检查你的连接列上有没有索引这个优先级比任何其他SQL调优手段都高。至于最外层GROUP BY的字段比如root_path如果遇到超大数据集可以考虑让它走hash聚合PostgreSQL默认对大结果集自动选择或者在root_path列上建索引但这通常只有查询频率特别高时才值得。6. 个人实践中的一些额外体会递归CTE和HAVING的缘份就是这样你可以让它们出现在同一条SQL里但必须让它们各就各位。递归CTE负责展开树的形状HAVING负责在形状完全展开之后对结果做聚合层面的裁剪。把过滤条件放到递归过程里用“逐行裁剪”替代“分组裁剪”就会得到和直觉完全相反的结果。我建议你在自己的库里建一张树形结构的测试表分别跑一下“完整展开外层HAVING”和“递归成员提前WHERE剪枝”两种写法直接对比结果。多跑几次之后你会形成一种肌肉记忆以后凡是看到递归CTE里有WHERE条件第一反应就是去验证这个条件是否会错误剪掉子树的子孙节点。这个直觉比记住任何语法规则都重要。另外如果不是特别必要尽量别在递归CTE上追求“一步到位”的神仙SQL。把递归展开和结果聚合分开写中间用临时表或WITH子句承接能调试、能分析、能看每一层产出了什么比一段炫技式的超长SQL靠谱得多。至少我维护过的几个核心报表系统里稳定可靠的方案都是这种“看起来不够高端但逻辑清楚”的写法。
