拓冰网站定制 
1. 递归查询的业务底色先搞清楚最上手和最下手到底指什么做数据开发这些年递归查询一直是个让人又爱又恨的东西。爱的是它能轻松搞定层级数据——组织架构、商品分类、评论回复、BOM物料清单这些树形结构只有递归才能优雅处理恨的是它一旦写不好轻则查错层级重则把数据库跑死。递归查询最上手信息以及递归查询最下手信息这个标题说白了就是一类非常具体的需求给定树形结构中的任意一个节点怎么快速定位到它所在分支的最顶层节点最上手以及它分支下的最底层叶子节点最下手。日常开发里这类场景太常见了——你要查某个门店隶属于哪个大区、某个末级分类挂在哪个一级类目下面、某个子任务归属的根项目是谁这就是最上手信息反过来你要统计某个一级分类下到底有多少个末级SKU、某个根节点下挂了多少层最末端的文件这就是最下手信息。但很多人在写这种查询时有个误区以为最上手/最下手就是简单地递归到某个固定深度。实际根本不是树的深度是不确定的你永远不知道这棵树长了多少层所以必须让递归自己走到头走到没有父节点的节点根或者没有子节点的节点叶子才算真正找到答案。我也是踩了不少坑才把这两类查询彻底理顺的。今天这篇就把最上手和最下手两条路线的实现思路、SQL写法、性能优化踩坑全部摊开说清楚用的全是实际生产环境里验证过的方案。下面先给出一张标准的树形表结构后面所有案例都基于它来跑。-- 组织架构表id 是节点IDpid 是父节点ID顶层节点的 pid 0 CREATE TABLE dept ( id INT PRIMARY KEY, pid INT NOT NULL DEFAULT 0, name VARCHAR(50) NOT NULL, sort_path VARCHAR(500) NULL COMMENT 物化路径缓存后面优化会用到 );这张表干净、直观只有id、pid、name三个核心字段。后续所有递归写法都以它为例你可以直接替换成自己的业务表。1.1 先给最上手和最下手一个明确定义我发现很多讨论把这俩概念搅在一起有些人说的最上手是指树最左边的节点有些人说的最下手是指树最右边的节点——这就完全偏了。我建议先明确词汇在本文中的固定含义避免后面越看越乱术语含义业务例子最上手信息层级树中最顶层祖先根节点的信息某门店的集团总部、某末级分类的一级类目最下手信息层级树中最底层后代叶子节点的信息某一级分类下所有没有子分类的末端商品这个定义非常关键。最上手是向上找找的是根最下手是向下找找的是叶子。两者遍历方向相反SQL写法也完全不同但都需要递归查询来兜底因为你没法预测树有几层。1.2 为什么不能用固定层级JOIN代替递归在没有递归语法之前层级数据查询都是靠多次LEFT JOIN每JOIN一次就往下走一层。这种方案在小规模、深度固定的场景下勉强能用但一旦遇到以下几种情况就会崩树的深度不确定——今天是3层明天业务扩张变成6层SQL就得改。深度暴涨时——比如评论楼中楼无限嵌套JOIN写10层、20层都是灾难。最下手节点深度不一——有些分支4层到底有些分支7层到底固定JOIN会出现大量空值。递归查询的价值恰恰在这里它把未知深度的遍历变成了循环直到边界动态扩展不需要预先知道树多深。这就是为什么掌握递归是处理层级数据的基本功。2. 定位最上手信息向上爬到根节点的三种写法给一个子节点找出它的根祖先——这是最上手信息查询的标准场景。举个例子给你部门ID105某个客服三组要求返回它所属的集团总部ID和名称。这类需求在权限数据隔离、报表汇总归口、数据血缘追溯里非常常见。我把写法拆成三种前两种是正统递归方案第三种是用窗口函数取巧但性能极好。实际开发中我推荐按数据量和个人习惯来选择。2.1 写法一自底向上的递归最符合直觉所谓自底向上就是从给定的子节点出发不断查它的父节点直到parent_id为0根为止。MySQL 8.0和PostgreSQL都支持WITH RECURSIVE写法是这样的WITH RECURSIVE up_tree AS ( -- 锚点从当前节点开始 SELECT id, pid, name, 1 AS lvl FROM dept WHERE id 105 UNION ALL -- 递归向上找父节点 SELECT d.id, d.pid, d.name, up.lvl 1 FROM dept d INNER JOIN up_tree up ON d.id up.pid WHERE up.pid 0 ) SELECT id, pid, name, lvl FROM up_tree ORDER BY lvl DESC LIMIT 1;这段SQL的逻辑很直白先拿到自己然后JOIN父节点、JOIN爷爷节点……一路向上。最终结果集里lvl最大那条记录就是根节点。不过有人说ORDER BY lvl DESC LIMIT 1不够稳因为在极端情况下比如循环引用导致重复行你可能取到异常值。更稳妥的做法是在递归过程中直接记录路径最后取路径的最后一个节点。我后面会专门讲循环引用的问题。2.2 写法二自顶向下递归后取最小层级反推法另一种思路是反过来以你手上的节点为假根向下递归出整棵子树然后把递归路径中层级最小的节点找出来。但这里有个前提——你得确保手上这个节点所在的树确实能追到同一个根。写法的SQL如下WITH RECURSIVE down_tree AS ( SELECT id, pid, name, 1 AS lvl, CAST(id AS CHAR(500)) AS path FROM dept WHERE id 105 UNION ALL SELECT d.id, d.pid, d.name, dt.lvl 1, CONCAT(dt.path, -, d.id) FROM dept d INNER JOIN down_tree dt ON d.pid dt.id ) SELECT id, pid, name, lvl, path FROM down_tree WHERE pid 0 OR lvl 1;其实这种写法有点绕它先向下展开子树再在结果集里找根。相比写法一它更适合一种特殊场景当你不知道给定的节点是不是中间节点、且需要同时输出从根到该节点的完整路径时。因为path字段把整条链路都记录下来了最上手信息就是链路的第一个节点最下手信息就是链路最后一个节点——等信息全部一次拿齐。2.3 写法三Oracle CONNECT BY 的 START WITH ... CONNECT BY PRIOR如果你用的是Oracle那就没必要写WITH RECURSIVE了。CONNECT BY天生就是为这种查询设计的一句话搞定SELECT id, pid, name, LEVEL FROM dept START WITH id 105 CONNECT BY PRIOR pid id ORDER BY LEVEL DESC FETCH FIRST 1 ROW ONLY;这里关键在于CONNECT BY PRIOR pid id——它的意思是按照上一行(prior)的pid等于当前行的id往上走。START WITH id 105指定出发点LEVEL自动递增。逻辑和写法一完全对应。需要提醒的是Oracle里如果不加ORDER BY LEVEL DESC处理查询结果的顺序是乱序的所以取最大LEVEL这一步必须显式做。2.4 三种写法的取舍对比方案数据库性能特点适用场景自底向上递归UNION ALLMySQL 8.0 / PG递归行数少只走这条分支通常更快单节点追溯根最常见需求自顶向下反推MySQL 8.0 / PG会展开整个子树节点多时慢需要同时拿完整路径、做血缘分析CONNECT BYOracleOracle内部优化成熟单条SQL简洁存量Oracle系统DBA最熟基于我的经验如果你只需要最上手的ID和名称首选写法一。它只沿着父亲链向上走不会横向扩散性能通常是最好的。3. 定位最下手信息叶子节点递归的正确打开方式最下手信息比最上手信息要麻烦得多因为根有一个明确判据——pid 0但叶子没有唯一的简单判据你只能靠不存在以我为父节点的子节点来判定。这个区别就是很多人卡壳的地方。3.1 叶子节点的精确判定NOT EXISTS先给出核心概念一个节点是叶子当且仅当没有任何其他节点的pid等于它的id。翻译成SQL就是SELECT id, name FROM dept d WHERE NOT EXISTS ( SELECT 1 FROM dept child WHERE child.pid d.id );但这样只能查出全表所有叶子如果你只需要指定根节点下的叶子就必须先递归出该根下的整个子树然后从中过滤叶子。3.2 从指定根节点出发找所有最下手节点以找出一级部门行政中心下所有末级部门为例WITH RECURSIVE down_tree AS ( -- 锚点根节点 SELECT id, pid, name, 1 AS depth FROM dept WHERE id 1 UNION ALL -- 递归向下展开子节点 SELECT d.id, d.pid, d.name, dt.depth 1 FROM dept d INNER JOIN down_tree dt ON d.pid dt.id ) SELECT dt.id, dt.name, dt.depth FROM down_tree dt WHERE NOT EXISTS ( SELECT 1 FROM dept child WHERE child.pid dt.id ) ORDER BY dt.depth DESC;这段SQL分两步第一步用递归CTE把指定根下所有后代捞出来第二步用NOT EXISTS过滤出其中没有子节点的部分。ORDER BY depth DESC让最深层级的叶子排最前方便你确认最下手的含义。用真实数据演示一下。假设dept表有这些数据idpidname10集团总部101行政中心201技术中心10110后勤组10210安保组20120研发一组20220研发二组2021202前端小组2022202后端小组当指定根节点id1时递归子树包含所有节点。其中NOT EXISTS判断后叶子是101、102、201、2021、2022。注意202研发二组虽然还有子节点所以它不是叶子。但如果需求是某个节点比如202下的最下手信息那就只递归202子树叶子就是2021和2022。这就是给定任意节点查询最下手的标准姿势。3.3 最下手信息的另一种理解树的最大深度叶子有些时候老板说最下手其实想表达的是最深的那一层节点而不是所有叶子。比如这个分类树最底层到第几级了最底层挂了哪些数据这两种需求要分清楚-- 方案A所有叶子节点业务上的末级 SELECT ... WHERE NOT EXISTS(子节点) -- 上面已经写了 -- 方案B深度最大的节点可能包含非叶子 WITH RECURSIVE down_tree AS (...) -- 同上 SELECT id, name, depth FROM down_tree ORDER BY depth DESC LIMIT 1;方案B在某些场景下反而更危险——如果树的某个中间节点是叶子但另一个分支刚好多了一层那方案B就会漏掉中间叶子。所以在接需求时一定要先和业务方对齐最下手到底指末端无子节点还是最深一层不然白做。3.4 反向业务给叶子找根最下手与最上手的组合查询现实需求里还有一个常见变体给一堆最下手末级节点反查它们各自对应的最上手根节点。这个组合必须把向上递归和向下递归组合起来或者用物化路径直接切开。如果你的表里有sort_path字段可以在写入数据时同步维护比如每次插入都拼上父路径那查询会变得异常简单SELECT id, SUBSTRING_INDEX(sort_path, /, 1) AS root_name, SUBSTRING_INDEX(sort_path, /, -1) AS leaf_name FROM dept WHERE NOT EXISTS (SELECT 1 FROM dept child WHERE child.pid dept.id);这就是物化路径的威力——用空间换时间把递归问题变成字符串切割问题。代价是写入时需要维护路径字段但这个代价通常比查询时递归要小得多。我见过太多项目在查询压力上来之后靠加一个path字段解决了所有递归性能头疼问题。这个优化思路我建议认真考虑特别是你的层级数据量超过十万的时候。4. 三类常见坑递归死循环、深度限制、性能陷阱递归查询代码写出来容易但跑挂也容易。我把这几年实际遇到的高频坑按症状-根因-解法梳理出来这些都是文档里不写但生产环境一定会碰到的。4.1 无限递归与循环引用最致命的坑树形表最怕出现A的父是BB的父是A这种脏数据。一旦出现递归会无限套娃直到数据库资源耗尽。你可能会说业务不会产生这种数据但现实是人工迁移数据、接口对接、批量导入时脏数据真的防不胜防。典型的错误场景某次从Excel导入组织架构行顺序问题导致部分节点的pid指向了还没导入的ID后来又修正时引入环状引用。于是查询直接报错或者卡死。解法一路径字段防循环推荐而且通用WITH RECURSIVE down_tree AS ( SELECT id, pid, name, CAST(id AS CHAR(1000)) AS path, 1 AS depth FROM dept WHERE id 1 UNION ALL SELECT d.id, d.pid, d.name, CONCAT(dt.path, ,, d.id), dt.depth 1 FROM dept d INNER JOIN down_tree dt ON d.pid dt.id WHERE dt.depth 20 -- 安全阀 AND FIND_IN_SET(d.id, dt.path) 0 -- 出现重复即停 ) SELECT * FROM down_tree;这里的核心思路是每深入一层就把沿途的id拼进path如果某个节点的id已经出现在path里说明形成了环立即终止该分支。WHERE dt.depth 20是一个安全阀即使业务环异常复杂也不会让递归无限跑下去。解法二查询前自检脏数据-- 找出循环引用存在两个节点互相指向对方或者成环 SELECT a.id, a.pid, b.id AS bid, b.pid AS bpid FROM dept a JOIN dept b ON a.id b.pid AND a.pid b.id WHERE a.id b.id;这种清理脚本适合定期跑作为数据质量监控的一部分。我一般会把它写进夜间任务有环就告警而不是等到业务查询炸了才排查。4.2 递归深度限制MySQL的1000次硬上限MySQL 8.0的WITH RECURSIVE有个默认限制cte_max_recursion_depth 1000。注意这1000限制的是递归迭代次数不是层数。假设你的树只有500层但每条路径的节点数碾过去也可能会超。而PostgreSQL默认是100更保守。一旦超过上限错误信息通常是Recursive query aborted after 1001 iterations。解决办法有两个在会话级别调大限制SET SESSION cte_max_recursion_depth 10000;在查询级别附加提示WITH RECURSIVE down_tree AS (...) -- MySQL 8.0.19 支持加 hint SELECT /* MAX_RECURSION_DEPTH(10000) */ ...;但我强烈建议调大上限只是治标你得先确认业务树的真实深度。如果一棵树真有1000层以上多半是数据结构出了问题比如循环引用或者递归路径失控更要紧的是查清为什么会有这么深的树而不是盲目放开限制。4.3 性能陷阱递归展开整棵树的指数级灾难自底向上递归找最上手通常性能可控但自顶向下递归找最下手有个大坑——如果你从根节点开始递归它会展开根下整棵树的所有节点。假设每个节点平均100个子节点5层就是100的5次方直接爆炸。优化思路有三个层次按优先级排序索引必须就位。pid字段一定要建索引这是递归JOIN的连接字段没索引等于全表扫描10万数据就能让你卡到怀疑人生。我见过最离谱的情况一张200万行的表没给pid建索引递归查询跑了15分钟加个索引30毫秒。能不递归就不递归。如果业务是查所有叶子直接用NOT EXISTS加索引查比递归后再过滤快得多。递归只在限定根节点下方查询时才有必要。物化路径是大招。前面提到维护sort_path字段查询最下手直接WHERE sort_path LIKE /1/10/% AND NOT EXISTS(子节点)把递归变成了一个范围扫描。这是处理超大数据量的常用方案。性能优化身位判断我一般按这个原则先查数据量级再决定是否上物化路径如果只是在百万级以下UNION ALL递归加索引通常够用。不要一上来就搞复杂架构过度设计在生产里也是坑。5. MySQL、PostgreSQL、Oracle的递归方言差异与迁移建议说到递归查询很多人以为SQL标准有统一写法——错了标准只定义了WITH RECURSIVE的通用形态各家的细节差异特别多。如果你在职场上频繁切换数据库很多公司 MySQL、PG、Oracle 并存下面这张对照表能帮你少踩一半的坑。5.1 三大数据库的递归语法对照能力MySQL 8.0PostgreSQLOracle递归CTEWITH RECURSIVEWITH RECURSIVE11g 支持但主要用 CONNECT BYCONNECT BY不支持不支持原生支持写法最简洁深度限制cte_max_recursion_depth默认1000PG 16前默认100PG 16可调整CONNECT BY由层级驱动有MAX_LEVEL保护循环检测手动用路径字段处理手动用路径字段处理系统自带 NOCYCLE 关键字路径拼接CONCAT、FIND_IN_SET排序需显式ORDER BY需显式ORDER BYORDER SIBLINGS BY可按层级排序5.2 各库的最上手/最下手写法微调Oracle专用方案——最轻松因为Oracle自带CONNECT BY还附带NOCYCLE和SYS_CONNECT_BY_PATH-- 最上手自底向上注意这里 PRIOR 的方向 SELECT id, name FROM dept START WITH id 105 CONNECT BY NOCYCLE PRIOR pid id ORDER BY LEVEL DESC FETCH FIRST 1 ROW ONLY; -- 最下手自顶向下找叶子 SELECT id, name FROM dept START WITH id 1 CONNECT BY NOCYCLE PRIOR id pid AND NOT EXISTS (SELECT 1 FROM dept d WHERE d.pid dept.id);NOCYCLE是Oracle良心功能一行关键字就解决了MySQL要写一大段路径判断才能解决的循环问题。PostgreSQL的写法和MySQL大同小异唯一要注意的是PG 16之前的默认递归深度只有100如果你的组织架构超过100层别笑真有一定先调max_recursive_query_iteration参数再跑。5.3 从Oracle迁到MySQL时最容易翻车的地方这几年很多企业把Oracle换成了MySQL迁移中遇到最多的递归问题有三个CONNECT BY直接迁移失败。不是所有查询都能简单改写成CTE特别是用到了SYS_CONNECT_BY_PATH和ORDER SIBLINGS BY的SQL改写后逻辑容易错。循环检测缺失。Oracle有NOCYCLEMySQL没有迁移后必须补上路径防环逻辑不然脏数据就能把日常任务打挂。性能量级差异。同样规模的树Oracle的老练优化器能压住的SQLMySQL可能因为索引选择问题跑很慢。迁移后一定要重新EXPLAIN重新验证执行计划不能迷信逻辑相同性能就相同。如果你目前正卡在迁移项目上我给的建议是先写一个统一的递归逻辑抽象层把向上找根和向下找叶封装成固定入参出参的查询各库分别实现业务层不感知差异。这个成本不高但能让你以后换库、升级都从容很多。5.4 一个简单的封装思路以最上手查询为例假设你有一个函数接口输入任意节点ID输出它的根节点信息。在MySQL里可以写成存储函数DELIMITER // CREATE FUNCTION get_root_node(node_id INT) RETURNS INT DETERMINISTIC BEGIN DECLARE curr_id INT DEFAULT node_id; DECLARE parent_id INT DEFAULT 0; WHILE curr_id IS NOT NULL DO SELECT pid INTO parent_id FROM dept WHERE id curr_id; IF parent_id IS NULL OR parent_id 0 THEN RETURN curr_id; END IF; SET curr_id parent_id; END WHILE; RETURN NULL; END // DELIMITER ;注意这种存储函数适合快速单点查询不适合大批量调用比如循环几万行时逐行调用函数。大批量场景还是老老实实用一条CTE一次查出所有节点的根会更划算。这个取舍也是我踩过坑才想明白的——一开始图方便用函数封装结果跑几千个节点时慢得不行后来批量改成CTE JOIN才解决。6. 实际项目中的一次完整排障从卡死到恢复的排查链路光讲理论总觉得隔了一层。我把今年年初遇到的一次真实故障完整复盘一下这是一个典型的最下手信息查询引发的生产事故排查链路值得参考。6.1 故障现象业务方反馈某个报表页面打开超时数据库CPU瞬间飙到100%。当时查了监控发现有一条SQL跑了20多分钟还没结束。SQL大概是这样-- 找出某个树形菜单下所有末级菜单 WITH RECURSIVE menu_tree AS ( SELECT id, pid, name FROM menu WHERE id 100 UNION ALL SELECT m.id, m.pid, m.name FROM menu m INNER JOIN menu_tree mt ON m.pid mt.id ) SELECT ... FROM menu_tree WHERE NOT EXISTS(...);报错日志显示Recursive query aborted after 1001 iterations。这说明递归迭代次数超过了MySQL默认的1000上限然后SQL转为失败重试反复重试导致CPU打满。6.2 排查步骤第一步我先跑了简单的诊断SQL看菜单表数据量SELECT COUNT(*) FROM menu; -- 结果12万行12万行不算多按理说递归不至于卡死。于是继续查最大深度WITH RECURSIVE depth_check AS ( SELECT id, pid, 1 AS depth FROM menu WHERE id 100 UNION ALL SELECT m.id, m.pid, dc.depth 1 FROM menu m INNER JOIN depth_check dc ON m.pid dc.id ) SELECT MAX(depth) FROM depth_check;结果出来的时候我愣了一下最大深度是3树竟然很浅。那为什么会迭代超1000次第二步我怀疑是循环引用。查了一遍是否有节点的pid指向了祖先节点或者两个节点互相指。一查发现果然有2个节点的pid写反了导致递归在某个环里无限打转。-- 查疑似环 SELECT child.id, child.pid, parent.pid AS parent_parent_id FROM menu child JOIN menu parent ON child.pid parent.id WHERE child.id parent.pid;第三步确认环的位置后我并没有直接改数据——生产库改数据要谨慎。我先让业务确认这两个节点的归属再由数据管理员修正同时把SQL里的递归加了防环逻辑路径字段记录遇到重复就stop。6.3 修复方案与验证最终修复分两步修数据修正两个节点的pid让环解开。改SQL在递归中加入路径防环安全阀避免未来再次出现脏数据时卡死。修改后的SQL长这样WITH RECURSIVE menu_tree AS ( SELECT id, pid, name, CAST(id AS CHAR(500)) AS path, 1 AS depth FROM menu WHERE id 100 UNION ALL SELECT m.id, m.pid, m.name, CONCAT(mt.path, ,, m.id), mt.depth 1 FROM menu m INNER JOIN menu_tree mt ON m.pid mt.id WHERE mt.depth 30 AND FIND_IN_SET(m.id, mt.path) 0 ) SELECT id, name, depth FROM menu_tree WHERE NOT EXISTS (SELECT 1 FROM menu child WHERE child.pid menu_tree.id) ORDER BY depth DESC;实测下来日志里再没出现过aborted报错这个页面从超时恢复到200ms以内。6.4 这个坑给我的三个教训第一递归查询的报错有时不是语法问题而是数据问题。看到aborted after 1001 iterations第一反应不该是调大上限而是查一下是不是有循环引用或极端深度的脏数据。第二数据质量监控很重要。这个环已经存在了一个多月但因为平时没有查询会递归到那个位置所以一直没暴露。如果早就有递归环检测脚本定期跑就不会等到业务页面崩溃才发现了。第三防环逻辑应该作为递归查询的默认配置。就像写SQL时大家习惯性加WHERE 11一样我现在的习惯是只要写递归第一件事就是先考虑这个递归会不会环、会不会太深。这不是过度设计是真的会救你狗命。7. 递归查询的综合案例从一个子节点同时取上界和下界最后把前面所有知识点串起来做一个综合案例。假设现在有个需求给定部门ID2022要求一次性查出它最上手的根部门和它最下手的叶子部门。这个需求很常见比如前端树形组件默认展开某条路径时既要知道这条路径的根用于面包屑又要知道当前节点下有哪些末级用于渲染叶子标签。7.1 核心思路两条递归分开写再合并因为向上和向下的方向不同最好分开写最后用UNION或分列展示。下面给出一个可以直接抄的完整SQLWITH RECURSIVE -- 向上递归找根 up_tree AS ( SELECT id, pid, name, 1 AS up_depth FROM dept WHERE id 2022 UNION ALL SELECT d.id, d.pid, d.name, ut.up_depth 1 FROM dept d INNER JOIN up_tree ut ON d.id ut.pid WHERE ut.pid 0 ), -- 向下递归找叶子 down_tree AS ( SELECT id, pid, name, 1 AS down_depth FROM dept WHERE id 2022 UNION ALL SELECT d.id, d.pid, d.name, dt.down_depth 1 FROM dept d INNER JOIN down_tree dt ON d.pid dt.id ) SELECT -- 最上手向上递归里层级最大的那行 (SELECT id FROM up_tree ORDER BY up_depth DESC LIMIT 1) AS root_id, (SELECT name FROM up_tree ORDER BY up_depth DESC LIMIT 1) AS root_name, -- 最下手向下递归里的叶子取最深那条 (SELECT id FROM down_tree WHERE NOT EXISTS (SELECT 1 FROM dept child WHERE child.pid down_tree.id) ORDER BY down_depth DESC LIMIT 1) AS leaf_id, (SELECT name FROM down_tree WHERE NOT EXISTS (SELECT 1 FROM dept child WHERE child.pid down_tree.id) ORDER BY down_depth DESC LIMIT 1) AS leaf_name;跑出来的结果一行搞定root_idroot_nameleaf_idleaf_name1集团总部20221前端小组看起来很不错对吧不过这句话要加个前提如果你的最下手要求是所有叶子中最深的那个且存在多个最深叶子上面SQL只会随机返回一个。如果你需要返回全部叶子列表那就要把最后两个子查询改成列表形式或者直接把down_tree的叶子结果集单独输出。7.2 批量场景整张表每个节点的根和叶子都查出来上面是单点查询。但有些分析场景需要给整张表的所有节点一次性补充根ID和叶子是否标记这时候逐节点递归会慢到没法用正确做法是一次递归全表、再做标记。思路是这样的先找全表根节点pid0然后从所有根节点向下递归全表递归的同时记录root_idWITH RECURSIVE all_tree AS ( SELECT id, pid, name, id AS root_id, 1 AS depth FROM dept WHERE pid 0 UNION ALL SELECT d.id, d.pid, d.name, at.root_id, at.depth 1 FROM dept d INNER JOIN all_tree at ON d.pid at.id ) SELECT id, name, root_id, CASE WHEN NOT EXISTS ( SELECT 1 FROM dept child WHERE child.pid all_tree.id ) THEN 1 ELSE 0 END AS is_leaf, depth FROM all_tree;这个查询把向上的根和向下的叶子一次性全部标记出来性能上只做了一次全树遍历比逐节点递归快几个数量级。如果你是做数据仓库的ETL加工、需要给宽表补充层级维度这个写法会经常用到。7.3 业务含义不清晰时先写伪代码再转SQL最后分享一个工作习惯。碰到最上手最下手这类模糊需求我习惯先和业务用伪代码对齐再落SQL。这个习惯救了我很多次特别是对接非技术背景的产品时。伪代码示例对每个节点 N 向上找直到父节点不存在返回顶层节点 向下找直到没有子节点返回所有末级节点翻译成SQL之前先把最上手/最下手的定义、边界条件比如空树、单节点树、环状数据全部列出来写到需求文档里让业务确认。单节点时它的最上手和最下手都是它自己这种边界往往最容易让测试用例挂掉因为开发潜意识里默认树一定有多层。根据个人经验递归查询写得对不对最有效的验证方式不是看单个SQL执行成功而是造三张测试树一棵正常多叉树、一棵单节点树、一棵带环的脏数据树三棵都跑通才能放心交给业务。这个测试组合成本极低但能挡住绝大多数生产事故。 递归查询实战：定位树形结构最上手与最下手节点的SQL方案
1. 递归查询的业务底色先搞清楚最上手和最下手到底指什么做数据开发这些年递归查询一直是个让人又爱又恨的东西。爱的是它能轻松搞定层级数据——组织架构、商品分类、评论回复、BOM物料清单这些树形结构只有递归才能优雅处理恨的是它一旦写不好轻则查错层级重则把数据库跑死。递归查询最上手信息以及递归查询最下手信息这个标题说白了就是一类非常具体的需求给定树形结构中的任意一个节点怎么快速定位到它所在分支的最顶层节点最上手以及它分支下的最底层叶子节点最下手。日常开发里这类场景太常见了——你要查某个门店隶属于哪个大区、某个末级分类挂在哪个一级类目下面、某个子任务归属的根项目是谁这就是最上手信息反过来你要统计某个一级分类下到底有多少个末级SKU、某个根节点下挂了多少层最末端的文件这就是最下手信息。但很多人在写这种查询时有个误区以为最上手/最下手就是简单地递归到某个固定深度。实际根本不是树的深度是不确定的你永远不知道这棵树长了多少层所以必须让递归自己走到头走到没有父节点的节点根或者没有子节点的节点叶子才算真正找到答案。我也是踩了不少坑才把这两类查询彻底理顺的。今天这篇就把最上手和最下手两条路线的实现思路、SQL写法、性能优化踩坑全部摊开说清楚用的全是实际生产环境里验证过的方案。下面先给出一张标准的树形表结构后面所有案例都基于它来跑。-- 组织架构表id 是节点IDpid 是父节点ID顶层节点的 pid 0 CREATE TABLE dept ( id INT PRIMARY KEY, pid INT NOT NULL DEFAULT 0, name VARCHAR(50) NOT NULL, sort_path VARCHAR(500) NULL COMMENT 物化路径缓存后面优化会用到 );这张表干净、直观只有id、pid、name三个核心字段。后续所有递归写法都以它为例你可以直接替换成自己的业务表。1.1 先给最上手和最下手一个明确定义我发现很多讨论把这俩概念搅在一起有些人说的最上手是指树最左边的节点有些人说的最下手是指树最右边的节点——这就完全偏了。我建议先明确词汇在本文中的固定含义避免后面越看越乱术语含义业务例子最上手信息层级树中最顶层祖先根节点的信息某门店的集团总部、某末级分类的一级类目最下手信息层级树中最底层后代叶子节点的信息某一级分类下所有没有子分类的末端商品这个定义非常关键。最上手是向上找找的是根最下手是向下找找的是叶子。两者遍历方向相反SQL写法也完全不同但都需要递归查询来兜底因为你没法预测树有几层。1.2 为什么不能用固定层级JOIN代替递归在没有递归语法之前层级数据查询都是靠多次LEFT JOIN每JOIN一次就往下走一层。这种方案在小规模、深度固定的场景下勉强能用但一旦遇到以下几种情况就会崩树的深度不确定——今天是3层明天业务扩张变成6层SQL就得改。深度暴涨时——比如评论楼中楼无限嵌套JOIN写10层、20层都是灾难。最下手节点深度不一——有些分支4层到底有些分支7层到底固定JOIN会出现大量空值。递归查询的价值恰恰在这里它把未知深度的遍历变成了循环直到边界动态扩展不需要预先知道树多深。这就是为什么掌握递归是处理层级数据的基本功。2. 定位最上手信息向上爬到根节点的三种写法给一个子节点找出它的根祖先——这是最上手信息查询的标准场景。举个例子给你部门ID105某个客服三组要求返回它所属的集团总部ID和名称。这类需求在权限数据隔离、报表汇总归口、数据血缘追溯里非常常见。我把写法拆成三种前两种是正统递归方案第三种是用窗口函数取巧但性能极好。实际开发中我推荐按数据量和个人习惯来选择。2.1 写法一自底向上的递归最符合直觉所谓自底向上就是从给定的子节点出发不断查它的父节点直到parent_id为0根为止。MySQL 8.0和PostgreSQL都支持WITH RECURSIVE写法是这样的WITH RECURSIVE up_tree AS ( -- 锚点从当前节点开始 SELECT id, pid, name, 1 AS lvl FROM dept WHERE id 105 UNION ALL -- 递归向上找父节点 SELECT d.id, d.pid, d.name, up.lvl 1 FROM dept d INNER JOIN up_tree up ON d.id up.pid WHERE up.pid 0 ) SELECT id, pid, name, lvl FROM up_tree ORDER BY lvl DESC LIMIT 1;这段SQL的逻辑很直白先拿到自己然后JOIN父节点、JOIN爷爷节点……一路向上。最终结果集里lvl最大那条记录就是根节点。不过有人说ORDER BY lvl DESC LIMIT 1不够稳因为在极端情况下比如循环引用导致重复行你可能取到异常值。更稳妥的做法是在递归过程中直接记录路径最后取路径的最后一个节点。我后面会专门讲循环引用的问题。2.2 写法二自顶向下递归后取最小层级反推法另一种思路是反过来以你手上的节点为假根向下递归出整棵子树然后把递归路径中层级最小的节点找出来。但这里有个前提——你得确保手上这个节点所在的树确实能追到同一个根。写法的SQL如下WITH RECURSIVE down_tree AS ( SELECT id, pid, name, 1 AS lvl, CAST(id AS CHAR(500)) AS path FROM dept WHERE id 105 UNION ALL SELECT d.id, d.pid, d.name, dt.lvl 1, CONCAT(dt.path, -, d.id) FROM dept d INNER JOIN down_tree dt ON d.pid dt.id ) SELECT id, pid, name, lvl, path FROM down_tree WHERE pid 0 OR lvl 1;其实这种写法有点绕它先向下展开子树再在结果集里找根。相比写法一它更适合一种特殊场景当你不知道给定的节点是不是中间节点、且需要同时输出从根到该节点的完整路径时。因为path字段把整条链路都记录下来了最上手信息就是链路的第一个节点最下手信息就是链路最后一个节点——等信息全部一次拿齐。2.3 写法三Oracle CONNECT BY 的 START WITH ... CONNECT BY PRIOR如果你用的是Oracle那就没必要写WITH RECURSIVE了。CONNECT BY天生就是为这种查询设计的一句话搞定SELECT id, pid, name, LEVEL FROM dept START WITH id 105 CONNECT BY PRIOR pid id ORDER BY LEVEL DESC FETCH FIRST 1 ROW ONLY;这里关键在于CONNECT BY PRIOR pid id——它的意思是按照上一行(prior)的pid等于当前行的id往上走。START WITH id 105指定出发点LEVEL自动递增。逻辑和写法一完全对应。需要提醒的是Oracle里如果不加ORDER BY LEVEL DESC处理查询结果的顺序是乱序的所以取最大LEVEL这一步必须显式做。2.4 三种写法的取舍对比方案数据库性能特点适用场景自底向上递归UNION ALLMySQL 8.0 / PG递归行数少只走这条分支通常更快单节点追溯根最常见需求自顶向下反推MySQL 8.0 / PG会展开整个子树节点多时慢需要同时拿完整路径、做血缘分析CONNECT BYOracleOracle内部优化成熟单条SQL简洁存量Oracle系统DBA最熟基于我的经验如果你只需要最上手的ID和名称首选写法一。它只沿着父亲链向上走不会横向扩散性能通常是最好的。3. 定位最下手信息叶子节点递归的正确打开方式最下手信息比最上手信息要麻烦得多因为根有一个明确判据——pid 0但叶子没有唯一的简单判据你只能靠不存在以我为父节点的子节点来判定。这个区别就是很多人卡壳的地方。3.1 叶子节点的精确判定NOT EXISTS先给出核心概念一个节点是叶子当且仅当没有任何其他节点的pid等于它的id。翻译成SQL就是SELECT id, name FROM dept d WHERE NOT EXISTS ( SELECT 1 FROM dept child WHERE child.pid d.id );但这样只能查出全表所有叶子如果你只需要指定根节点下的叶子就必须先递归出该根下的整个子树然后从中过滤叶子。3.2 从指定根节点出发找所有最下手节点以找出一级部门行政中心下所有末级部门为例WITH RECURSIVE down_tree AS ( -- 锚点根节点 SELECT id, pid, name, 1 AS depth FROM dept WHERE id 1 UNION ALL -- 递归向下展开子节点 SELECT d.id, d.pid, d.name, dt.depth 1 FROM dept d INNER JOIN down_tree dt ON d.pid dt.id ) SELECT dt.id, dt.name, dt.depth FROM down_tree dt WHERE NOT EXISTS ( SELECT 1 FROM dept child WHERE child.pid dt.id ) ORDER BY dt.depth DESC;这段SQL分两步第一步用递归CTE把指定根下所有后代捞出来第二步用NOT EXISTS过滤出其中没有子节点的部分。ORDER BY depth DESC让最深层级的叶子排最前方便你确认最下手的含义。用真实数据演示一下。假设dept表有这些数据idpidname10集团总部101行政中心201技术中心10110后勤组10210安保组20120研发一组20220研发二组2021202前端小组2022202后端小组当指定根节点id1时递归子树包含所有节点。其中NOT EXISTS判断后叶子是101、102、201、2021、2022。注意202研发二组虽然还有子节点所以它不是叶子。但如果需求是某个节点比如202下的最下手信息那就只递归202子树叶子就是2021和2022。这就是给定任意节点查询最下手的标准姿势。3.3 最下手信息的另一种理解树的最大深度叶子有些时候老板说最下手其实想表达的是最深的那一层节点而不是所有叶子。比如这个分类树最底层到第几级了最底层挂了哪些数据这两种需求要分清楚-- 方案A所有叶子节点业务上的末级 SELECT ... WHERE NOT EXISTS(子节点) -- 上面已经写了 -- 方案B深度最大的节点可能包含非叶子 WITH RECURSIVE down_tree AS (...) -- 同上 SELECT id, name, depth FROM down_tree ORDER BY depth DESC LIMIT 1;方案B在某些场景下反而更危险——如果树的某个中间节点是叶子但另一个分支刚好多了一层那方案B就会漏掉中间叶子。所以在接需求时一定要先和业务方对齐最下手到底指末端无子节点还是最深一层不然白做。3.4 反向业务给叶子找根最下手与最上手的组合查询现实需求里还有一个常见变体给一堆最下手末级节点反查它们各自对应的最上手根节点。这个组合必须把向上递归和向下递归组合起来或者用物化路径直接切开。如果你的表里有sort_path字段可以在写入数据时同步维护比如每次插入都拼上父路径那查询会变得异常简单SELECT id, SUBSTRING_INDEX(sort_path, /, 1) AS root_name, SUBSTRING_INDEX(sort_path, /, -1) AS leaf_name FROM dept WHERE NOT EXISTS (SELECT 1 FROM dept child WHERE child.pid dept.id);这就是物化路径的威力——用空间换时间把递归问题变成字符串切割问题。代价是写入时需要维护路径字段但这个代价通常比查询时递归要小得多。我见过太多项目在查询压力上来之后靠加一个path字段解决了所有递归性能头疼问题。这个优化思路我建议认真考虑特别是你的层级数据量超过十万的时候。4. 三类常见坑递归死循环、深度限制、性能陷阱递归查询代码写出来容易但跑挂也容易。我把这几年实际遇到的高频坑按症状-根因-解法梳理出来这些都是文档里不写但生产环境一定会碰到的。4.1 无限递归与循环引用最致命的坑树形表最怕出现A的父是BB的父是A这种脏数据。一旦出现递归会无限套娃直到数据库资源耗尽。你可能会说业务不会产生这种数据但现实是人工迁移数据、接口对接、批量导入时脏数据真的防不胜防。典型的错误场景某次从Excel导入组织架构行顺序问题导致部分节点的pid指向了还没导入的ID后来又修正时引入环状引用。于是查询直接报错或者卡死。解法一路径字段防循环推荐而且通用WITH RECURSIVE down_tree AS ( SELECT id, pid, name, CAST(id AS CHAR(1000)) AS path, 1 AS depth FROM dept WHERE id 1 UNION ALL SELECT d.id, d.pid, d.name, CONCAT(dt.path, ,, d.id), dt.depth 1 FROM dept d INNER JOIN down_tree dt ON d.pid dt.id WHERE dt.depth 20 -- 安全阀 AND FIND_IN_SET(d.id, dt.path) 0 -- 出现重复即停 ) SELECT * FROM down_tree;这里的核心思路是每深入一层就把沿途的id拼进path如果某个节点的id已经出现在path里说明形成了环立即终止该分支。WHERE dt.depth 20是一个安全阀即使业务环异常复杂也不会让递归无限跑下去。解法二查询前自检脏数据-- 找出循环引用存在两个节点互相指向对方或者成环 SELECT a.id, a.pid, b.id AS bid, b.pid AS bpid FROM dept a JOIN dept b ON a.id b.pid AND a.pid b.id WHERE a.id b.id;这种清理脚本适合定期跑作为数据质量监控的一部分。我一般会把它写进夜间任务有环就告警而不是等到业务查询炸了才排查。4.2 递归深度限制MySQL的1000次硬上限MySQL 8.0的WITH RECURSIVE有个默认限制cte_max_recursion_depth 1000。注意这1000限制的是递归迭代次数不是层数。假设你的树只有500层但每条路径的节点数碾过去也可能会超。而PostgreSQL默认是100更保守。一旦超过上限错误信息通常是Recursive query aborted after 1001 iterations。解决办法有两个在会话级别调大限制SET SESSION cte_max_recursion_depth 10000;在查询级别附加提示WITH RECURSIVE down_tree AS (...) -- MySQL 8.0.19 支持加 hint SELECT /* MAX_RECURSION_DEPTH(10000) */ ...;但我强烈建议调大上限只是治标你得先确认业务树的真实深度。如果一棵树真有1000层以上多半是数据结构出了问题比如循环引用或者递归路径失控更要紧的是查清为什么会有这么深的树而不是盲目放开限制。4.3 性能陷阱递归展开整棵树的指数级灾难自底向上递归找最上手通常性能可控但自顶向下递归找最下手有个大坑——如果你从根节点开始递归它会展开根下整棵树的所有节点。假设每个节点平均100个子节点5层就是100的5次方直接爆炸。优化思路有三个层次按优先级排序索引必须就位。pid字段一定要建索引这是递归JOIN的连接字段没索引等于全表扫描10万数据就能让你卡到怀疑人生。我见过最离谱的情况一张200万行的表没给pid建索引递归查询跑了15分钟加个索引30毫秒。能不递归就不递归。如果业务是查所有叶子直接用NOT EXISTS加索引查比递归后再过滤快得多。递归只在限定根节点下方查询时才有必要。物化路径是大招。前面提到维护sort_path字段查询最下手直接WHERE sort_path LIKE /1/10/% AND NOT EXISTS(子节点)把递归变成了一个范围扫描。这是处理超大数据量的常用方案。性能优化身位判断我一般按这个原则先查数据量级再决定是否上物化路径如果只是在百万级以下UNION ALL递归加索引通常够用。不要一上来就搞复杂架构过度设计在生产里也是坑。5. MySQL、PostgreSQL、Oracle的递归方言差异与迁移建议说到递归查询很多人以为SQL标准有统一写法——错了标准只定义了WITH RECURSIVE的通用形态各家的细节差异特别多。如果你在职场上频繁切换数据库很多公司 MySQL、PG、Oracle 并存下面这张对照表能帮你少踩一半的坑。5.1 三大数据库的递归语法对照能力MySQL 8.0PostgreSQLOracle递归CTEWITH RECURSIVEWITH RECURSIVE11g 支持但主要用 CONNECT BYCONNECT BY不支持不支持原生支持写法最简洁深度限制cte_max_recursion_depth默认1000PG 16前默认100PG 16可调整CONNECT BY由层级驱动有MAX_LEVEL保护循环检测手动用路径字段处理手动用路径字段处理系统自带 NOCYCLE 关键字路径拼接CONCAT、FIND_IN_SET排序需显式ORDER BY需显式ORDER BYORDER SIBLINGS BY可按层级排序5.2 各库的最上手/最下手写法微调Oracle专用方案——最轻松因为Oracle自带CONNECT BY还附带NOCYCLE和SYS_CONNECT_BY_PATH-- 最上手自底向上注意这里 PRIOR 的方向 SELECT id, name FROM dept START WITH id 105 CONNECT BY NOCYCLE PRIOR pid id ORDER BY LEVEL DESC FETCH FIRST 1 ROW ONLY; -- 最下手自顶向下找叶子 SELECT id, name FROM dept START WITH id 1 CONNECT BY NOCYCLE PRIOR id pid AND NOT EXISTS (SELECT 1 FROM dept d WHERE d.pid dept.id);NOCYCLE是Oracle良心功能一行关键字就解决了MySQL要写一大段路径判断才能解决的循环问题。PostgreSQL的写法和MySQL大同小异唯一要注意的是PG 16之前的默认递归深度只有100如果你的组织架构超过100层别笑真有一定先调max_recursive_query_iteration参数再跑。5.3 从Oracle迁到MySQL时最容易翻车的地方这几年很多企业把Oracle换成了MySQL迁移中遇到最多的递归问题有三个CONNECT BY直接迁移失败。不是所有查询都能简单改写成CTE特别是用到了SYS_CONNECT_BY_PATH和ORDER SIBLINGS BY的SQL改写后逻辑容易错。循环检测缺失。Oracle有NOCYCLEMySQL没有迁移后必须补上路径防环逻辑不然脏数据就能把日常任务打挂。性能量级差异。同样规模的树Oracle的老练优化器能压住的SQLMySQL可能因为索引选择问题跑很慢。迁移后一定要重新EXPLAIN重新验证执行计划不能迷信逻辑相同性能就相同。如果你目前正卡在迁移项目上我给的建议是先写一个统一的递归逻辑抽象层把向上找根和向下找叶封装成固定入参出参的查询各库分别实现业务层不感知差异。这个成本不高但能让你以后换库、升级都从容很多。5.4 一个简单的封装思路以最上手查询为例假设你有一个函数接口输入任意节点ID输出它的根节点信息。在MySQL里可以写成存储函数DELIMITER // CREATE FUNCTION get_root_node(node_id INT) RETURNS INT DETERMINISTIC BEGIN DECLARE curr_id INT DEFAULT node_id; DECLARE parent_id INT DEFAULT 0; WHILE curr_id IS NOT NULL DO SELECT pid INTO parent_id FROM dept WHERE id curr_id; IF parent_id IS NULL OR parent_id 0 THEN RETURN curr_id; END IF; SET curr_id parent_id; END WHILE; RETURN NULL; END // DELIMITER ;注意这种存储函数适合快速单点查询不适合大批量调用比如循环几万行时逐行调用函数。大批量场景还是老老实实用一条CTE一次查出所有节点的根会更划算。这个取舍也是我踩过坑才想明白的——一开始图方便用函数封装结果跑几千个节点时慢得不行后来批量改成CTE JOIN才解决。6. 实际项目中的一次完整排障从卡死到恢复的排查链路光讲理论总觉得隔了一层。我把今年年初遇到的一次真实故障完整复盘一下这是一个典型的最下手信息查询引发的生产事故排查链路值得参考。6.1 故障现象业务方反馈某个报表页面打开超时数据库CPU瞬间飙到100%。当时查了监控发现有一条SQL跑了20多分钟还没结束。SQL大概是这样-- 找出某个树形菜单下所有末级菜单 WITH RECURSIVE menu_tree AS ( SELECT id, pid, name FROM menu WHERE id 100 UNION ALL SELECT m.id, m.pid, m.name FROM menu m INNER JOIN menu_tree mt ON m.pid mt.id ) SELECT ... FROM menu_tree WHERE NOT EXISTS(...);报错日志显示Recursive query aborted after 1001 iterations。这说明递归迭代次数超过了MySQL默认的1000上限然后SQL转为失败重试反复重试导致CPU打满。6.2 排查步骤第一步我先跑了简单的诊断SQL看菜单表数据量SELECT COUNT(*) FROM menu; -- 结果12万行12万行不算多按理说递归不至于卡死。于是继续查最大深度WITH RECURSIVE depth_check AS ( SELECT id, pid, 1 AS depth FROM menu WHERE id 100 UNION ALL SELECT m.id, m.pid, dc.depth 1 FROM menu m INNER JOIN depth_check dc ON m.pid dc.id ) SELECT MAX(depth) FROM depth_check;结果出来的时候我愣了一下最大深度是3树竟然很浅。那为什么会迭代超1000次第二步我怀疑是循环引用。查了一遍是否有节点的pid指向了祖先节点或者两个节点互相指。一查发现果然有2个节点的pid写反了导致递归在某个环里无限打转。-- 查疑似环 SELECT child.id, child.pid, parent.pid AS parent_parent_id FROM menu child JOIN menu parent ON child.pid parent.id WHERE child.id parent.pid;第三步确认环的位置后我并没有直接改数据——生产库改数据要谨慎。我先让业务确认这两个节点的归属再由数据管理员修正同时把SQL里的递归加了防环逻辑路径字段记录遇到重复就stop。6.3 修复方案与验证最终修复分两步修数据修正两个节点的pid让环解开。改SQL在递归中加入路径防环安全阀避免未来再次出现脏数据时卡死。修改后的SQL长这样WITH RECURSIVE menu_tree AS ( SELECT id, pid, name, CAST(id AS CHAR(500)) AS path, 1 AS depth FROM menu WHERE id 100 UNION ALL SELECT m.id, m.pid, m.name, CONCAT(mt.path, ,, m.id), mt.depth 1 FROM menu m INNER JOIN menu_tree mt ON m.pid mt.id WHERE mt.depth 30 AND FIND_IN_SET(m.id, mt.path) 0 ) SELECT id, name, depth FROM menu_tree WHERE NOT EXISTS (SELECT 1 FROM menu child WHERE child.pid menu_tree.id) ORDER BY depth DESC;实测下来日志里再没出现过aborted报错这个页面从超时恢复到200ms以内。6.4 这个坑给我的三个教训第一递归查询的报错有时不是语法问题而是数据问题。看到aborted after 1001 iterations第一反应不该是调大上限而是查一下是不是有循环引用或极端深度的脏数据。第二数据质量监控很重要。这个环已经存在了一个多月但因为平时没有查询会递归到那个位置所以一直没暴露。如果早就有递归环检测脚本定期跑就不会等到业务页面崩溃才发现了。第三防环逻辑应该作为递归查询的默认配置。就像写SQL时大家习惯性加WHERE 11一样我现在的习惯是只要写递归第一件事就是先考虑这个递归会不会环、会不会太深。这不是过度设计是真的会救你狗命。7. 递归查询的综合案例从一个子节点同时取上界和下界最后把前面所有知识点串起来做一个综合案例。假设现在有个需求给定部门ID2022要求一次性查出它最上手的根部门和它最下手的叶子部门。这个需求很常见比如前端树形组件默认展开某条路径时既要知道这条路径的根用于面包屑又要知道当前节点下有哪些末级用于渲染叶子标签。7.1 核心思路两条递归分开写再合并因为向上和向下的方向不同最好分开写最后用UNION或分列展示。下面给出一个可以直接抄的完整SQLWITH RECURSIVE -- 向上递归找根 up_tree AS ( SELECT id, pid, name, 1 AS up_depth FROM dept WHERE id 2022 UNION ALL SELECT d.id, d.pid, d.name, ut.up_depth 1 FROM dept d INNER JOIN up_tree ut ON d.id ut.pid WHERE ut.pid 0 ), -- 向下递归找叶子 down_tree AS ( SELECT id, pid, name, 1 AS down_depth FROM dept WHERE id 2022 UNION ALL SELECT d.id, d.pid, d.name, dt.down_depth 1 FROM dept d INNER JOIN down_tree dt ON d.pid dt.id ) SELECT -- 最上手向上递归里层级最大的那行 (SELECT id FROM up_tree ORDER BY up_depth DESC LIMIT 1) AS root_id, (SELECT name FROM up_tree ORDER BY up_depth DESC LIMIT 1) AS root_name, -- 最下手向下递归里的叶子取最深那条 (SELECT id FROM down_tree WHERE NOT EXISTS (SELECT 1 FROM dept child WHERE child.pid down_tree.id) ORDER BY down_depth DESC LIMIT 1) AS leaf_id, (SELECT name FROM down_tree WHERE NOT EXISTS (SELECT 1 FROM dept child WHERE child.pid down_tree.id) ORDER BY down_depth DESC LIMIT 1) AS leaf_name;跑出来的结果一行搞定root_idroot_nameleaf_idleaf_name1集团总部20221前端小组看起来很不错对吧不过这句话要加个前提如果你的最下手要求是所有叶子中最深的那个且存在多个最深叶子上面SQL只会随机返回一个。如果你需要返回全部叶子列表那就要把最后两个子查询改成列表形式或者直接把down_tree的叶子结果集单独输出。7.2 批量场景整张表每个节点的根和叶子都查出来上面是单点查询。但有些分析场景需要给整张表的所有节点一次性补充根ID和叶子是否标记这时候逐节点递归会慢到没法用正确做法是一次递归全表、再做标记。思路是这样的先找全表根节点pid0然后从所有根节点向下递归全表递归的同时记录root_idWITH RECURSIVE all_tree AS ( SELECT id, pid, name, id AS root_id, 1 AS depth FROM dept WHERE pid 0 UNION ALL SELECT d.id, d.pid, d.name, at.root_id, at.depth 1 FROM dept d INNER JOIN all_tree at ON d.pid at.id ) SELECT id, name, root_id, CASE WHEN NOT EXISTS ( SELECT 1 FROM dept child WHERE child.pid all_tree.id ) THEN 1 ELSE 0 END AS is_leaf, depth FROM all_tree;这个查询把向上的根和向下的叶子一次性全部标记出来性能上只做了一次全树遍历比逐节点递归快几个数量级。如果你是做数据仓库的ETL加工、需要给宽表补充层级维度这个写法会经常用到。7.3 业务含义不清晰时先写伪代码再转SQL最后分享一个工作习惯。碰到最上手最下手这类模糊需求我习惯先和业务用伪代码对齐再落SQL。这个习惯救了我很多次特别是对接非技术背景的产品时。伪代码示例对每个节点 N 向上找直到父节点不存在返回顶层节点 向下找直到没有子节点返回所有末级节点翻译成SQL之前先把最上手/最下手的定义、边界条件比如空树、单节点树、环状数据全部列出来写到需求文档里让业务确认。单节点时它的最上手和最下手都是它自己这种边界往往最容易让测试用例挂掉因为开发潜意识里默认树一定有多层。根据个人经验递归查询写得对不对最有效的验证方式不是看单个SQL执行成功而是造三张测试树一棵正常多叉树、一棵单节点树、一棵带环的脏数据树三棵都跑通才能放心交给业务。这个测试组合成本极低但能挡住绝大多数生产事故。