SQL递归查询实战：轻松搞定根节点与叶子节点提取

1. 递归查询的核心概念与适用场景递归查询这玩意儿搞数据库的人迟早都会碰上。我第一次接触是因为要做组织架构的层级查询一张部门表里存着parent_id想查出某个部门下面所有子部门直接写SQL会把人累死用递归一次搞定。说白了递归查询就是在SQL里用公用表表达式CTE实现自己调用自己的逻辑专门对付那些有父子关系、上下级关系的数据结构。最典型的场景就是树形结构数据。比如一个公司有总公司、分公司、部门、小组层级不固定可能是三层也可能是五层比如电商分类电子产品下面有手机手机下面有安卓、苹果再比如BOM表一个成品由多个部件组成部件又由更小的零件组成。这些数据有个共同点每条记录都有一个指向父记录的ID通过这个ID串成一条链条。递归查询就是沿着这条链条一直往下或往上钻钻到你想停的位置为止。那“最上手信息”和“最下手信息”这两个词我理解为最顶层的根节点信息和最底层的叶子节点信息。举个例子部门树里“最上手”就是总公司这个根没有父节点的那个“最下手”就是最末端的小组再也没有子部门的那些。很多业务需求就需要你准确抓出这两类节点比如统计有几个顶级部门、盘点哪些是末级岗位、找出一棵树里所有的根和叶。这篇文章就把这个事彻底讲透从原理到实操再到踩坑一次性给你说清楚。2. 递归查询的基本写法与执行逻辑2.1 递归CTE的结构拆解在SQL里写递归查询标准做法是用WITH RECURSIVEPostgreSQL、MySQL 8.0或者WITH ... ASSQL Server、Oracle但核心结构都一样一个锚点成员anchor member加一个递归成员recursive member中间用UNION ALL连起来。锚点成员是递归的起点一般就是查询最顶层的记录比如WHERE parent_id IS NULL或者P_Id0设定根。递归成员是反复执行的部分每次把上一次查出来的结果当作输入去查它们的子节点。两个部分拼在一起就完成了一次迭代。我习惯把它想象成挖地洞锚点挖第一锹递归成员顺着洞壁一直往下挖挖一层接一层直到挖不动为止。这里有个特别容易忽略的点递归成员里必须引用CTE自身的名称而锚点成员里不能引用。如果你写反了或者忘了数据库直接报错。另外一个关键点是UNION和UNION ALL的选择。绝大多数递归场景用UNION ALL就够了因为树的每个节点ID是有唯一性的不需要去重如果你用了UNION数据库会在每次迭代都做一次去重操作性能白白降一半而且语义上也容易出问题。2.2 递归的执行过程到底发生了什么很多新手学递归CTE代码能跑通但心里没底总觉得自己没真正理解。我换个方式讲数据库执行递归查询时不是真的“递归”而是迭代。先执行锚点查询得到第一批结果集然后拿着这个结果集去执行递归成员得到第二批结果集再拿第二批去执行得到第三批……直到某一次递归成员查不到任何记录了迭代结束最后把所有批次的结果用UNION ALL拼在一起返回。这个过程可以用一个“层级深度”字段来观察。你可以在每次递归时给记录的深度加1比如锚点深度为0第一次递归出来的深度为1第二次为2这样就能清楚看到每一步迭代产生了哪一层的数据。我经常在开发调试时加上这个字段把每层数据分开检查问题立刻就能定位。PostgreSQL还有一个verbose参数可以让你看到每次迭代实际处理了多少行。在事务里跑EXPLAIN ANALYZE WITH RECURSIVE ... 的时候它会把每轮的扫描行数和时间都列出来。我遇到查询性能差的时候第一件事就是看这个输出确认递归是不是多层都做了全表扫描而不是索引查找。3. 如何拿到“最上手”信息根节点的抓取3.1 根节点的定义与两种判定条件先定义清楚什么叫做“根节点”。在递归查询的语境里根节点就是没有父节点的节点也就是树的最顶端。判断一个节点是不是根最直观的办法是看它的父ID是否为NULL或者父ID指向一个不存在的记录。实践中常见的设计是用0或者-1表示“无父节点”这种情况下得额外留意因为NULL判断和值判断是两种写法。拿到根节点有个简单粗暴的方法先跑完整的递归CTE把整棵树带深度拉出来然后筛掉那些有父节点的记录。但有父节点的记录怎么判断很简单递归CTE里带上根ID和路径如果一个节点的路径只有它自己那它必然就是根。还有一种更高效的做法直接对着原始表查WHERE parent_id IS NULL根本不用递归。这个我后面会详细对比。3.2 在递归结果中提取根节点如果你只想找整棵树的根那确实可以直接查原始表不必动用递归。但如果你已经跑了一个递归CTE想顺便把根揪出来那可以在递归结果里加一个标记。比如在锚点查询时把根ID设为自己的ID递归时把上一层传下来的根ID原样传下去这样每一行都带着它所属的根节点。最后想找根节点就直接WHERE id root_id。示例代码PostgreSQLWITH RECURSIVE tree AS ( SELECT id, parent_id, name, id AS root_id FROM dept WHERE parent_id IS NULL UNION ALL SELECT c.id, c.parent_id, c.name, p.root_id FROM dept c JOIN tree p ON c.parent_id p.id ) SELECT * FROM tree WHERE id root_id;这个查询里锚点把root_id设为自己的id递归时继承父行的root_id。所以最终每一行都能知道自己是哪个根的子孙而根节点自己的root_id就等于id。要抓根筛选条件就写id root_id。这个思路还可扩展想按根分组统计数据时直接GROUP BY root_id非常方便。3.3 根节点查询的实操注意事项实际项目里根节点往往不止一个。比如系统允许有多个顶级分类或多个独立的总部门这时“最上手信息”就是一群根节点。我的习惯是先查一遍原始表看父ID为空或者父ID0的记录有多少确认根的数量是否符合预期。如果预期是一个根却查出来多个八成是数据里有脏数据父ID没清干净。还有一个坑有些表设计里根节点的parent_id不是NULL而是0而且0这个ID在表里并不存在。这时候你写WHERE parent_id IS NULL会漏数据。更安全的是写WHERE parent_id IS NULL OR parent_id 0或者干脆判断parent_id NOT IN (SELECT id FROM dept)。我见过不止一个项目因为这个问题顶层数据统计少了几条排查了半天。性能方面如果你只需要根节点千万千万别跑完整递归再过滤。直接查原始表加条件那一行索引定位就搞定了。递归是用来算路径、算深度、算子树用的不是用来大海捞针的。4. 如何拿到“最下手”信息叶子节点的抓取4.1 叶子节点的判定逻辑叶子节点就是没有任何子节点的节点树形结构里的末端。拿部门来说最底层的小组就是叶子拿商品分类来说最小的子分类就是叶子。怎么判断叶子节点最直接的办法这个节点的ID没有出现在任何其他记录的parent_id里。换句话讲如果在原始表里做一次反向检查查“哪些ID不在parent_id列里”这些ID对应的记录就是叶子。但写SQL的时候要注意parent_id里可能包含NULL或者0这不算真引用。所以判断时要用NOT EXISTS或者LEFT JOIN查“没有被任何记录引用为父的节点”。4.2 在递归结果中提取叶子节点如果你已经跑了一个递归CTE想从结果里筛出叶子节点那思路就变成查出所有节点的ID集合再看看哪些ID没被任何节点的parent_id引用。但递归结果里可能只包含从某个根出发的部分子树所以你得确定要查的范围。如果全树都要叶子那直接在原始表上做反查最省事。示例代码标准SQLSELECT d.* FROM dept d WHERE NOT EXISTS ( SELECT 1 FROM dept c WHERE c.parent_id d.id );这里用了NOT EXISTS如果有某个记录的parent_id等于当前记录的id那说明当前记录有孩子不是叶子反过来说没有孩子就是叶子。这个写法性能不错尤其dept.id有主键索引、parent_id有普通索引的时候。如果递归CTE里想顺手带出叶子标记可以在递归结果里加一个布尔值。锚点或者每一层先默认自己是叶子递归时如果发现有子节点就把父行的叶子标记改为false。但实际写起来比较绕我建议还是直接用NOT EXISTS反查简洁明确不容易出逻辑错误。4.3 叶子节点查询的实操注意事项叶子节点查询最大的坑在“孤儿数据”。什么叫孤儿数据就是有一条记录的parent_id指向一个不存在的ID。这种数据在递归查询里不会被任何节点引到所以在反查叶子的时候它本身作为“没有孩子”的记录会被误判成叶子但它同时也不是任何节点的孩子本质上是个脏数据。我的处理习惯是在判断叶子之前先排除那些parent_id不存在的记录也就是把孤儿数据隔离出来单独处理。第二个坑是“自引用”数据。有些系统的根节点会把自己的parent_id设成自己或者把parent_id设为自己的ID来表示“无父”。这种自引用会让NOT EXISTS查询失效因为它会查到自己看似自己引用了自己导致判定不准确。所以写叶子查询之前一定要先确认数据设计有没有自引用模式。第三个坑是性能。如果表特别大NOT EXISTS里的子查询每次都要扫全部数据那会非常慢。我的经验是先给parent_id建索引同时在递归CTE里尽量限定根节点范围缩小检查域。如果只是统计叶子数量可以用COUNT NOT EXISTS配合但注意去重避免重复统计。5. 一个完整实例部门树里的顶级与末级查询5.1 准备测试数据光讲理论没意思我实际搭一个简单的部门表来演示。假设有这么一张表CREATE TABLE dept ( id INT PRIMARY KEY, parent_id INT, name TEXT );插入一组数据模拟一个公司的组织架构。总公司id1没有父下面有技术部id2、人事部id3技术部下面有前端组id4、后端组id5后端组下面又有Java组id6。这样树的根是1叶子是3、4、6人事部、前端组、Java组。INSERT INTO dept VALUES (1, NULL, 总公司), (2, 1, 技术部), (3, 1, 人事部), (4, 2, 前端组), (5, 2, 后端组), (6, 5, Java组);注意这里id3是没有孩子的所以是叶子id4也没有id6也没有。id6是id5的孩子而id5是id2的孩子所以从根1一路下来层级是1→2→5→6。5.2 查询根节点和叶子节点的完整SQL根节点查询也就是“最上手信息”SELECT * FROM dept WHERE parent_id IS NULL;这一步直接就能拿到总公司这一行用不上递归。叶子节点查询也就是“最下手信息”SELECT d.* FROM dept d WHERE NOT EXISTS ( SELECT 1 FROM dept c WHERE c.parent_id d.id );执行结果会输出id3、id4、id6这三行正好是三个末端部门。如果我想在同一个查询里既显示根又显示叶可以用UNION ALL拼在一起或者加一个类型标记。比如SELECT id, name, root AS node_type FROM dept WHERE parent_id IS NULL UNION ALL SELECT id, name, leaf AS node_type FROM dept d WHERE NOT EXISTS (SELECT 1 FROM dept c WHERE c.parent_id d.id);这样一次查询就可以同时看到顶和底非常直观。实际业务里经常要做一个树形界面的“根节点标签”和“叶子节点标签”这个查询就是基础。5.3 带路径和深度的递归综合查询有时候你不只想找根和叶还想知道整棵树的路径和层级。这时递归就派上用场了。以下SQL会把每一条从根到每个节点的完整路径都算出来WITH RECURSIVE tree AS ( SELECT id, parent_id, name, ARRAY[id] AS path, 0 AS depth FROM dept WHERE parent_id IS NULL UNION ALL SELECT c.id, c.parent_id, c.name, p.path || c.id, p.depth 1 FROM dept c JOIN tree p ON c.parent_id p.id ) SELECT id, name, depth, array_to_string(path, -) AS path_display FROM tree ORDER BY depth, id;这段代码里锚点把path设成一个只包含自己ID的数组深度为0递归时每往下一层就在path数组后面追加子节点的ID深度加1。最后用array_to_string把数组展成类似“1-2-5-6”的字符串。有了path和depth你就能在结果里标记根和叶子depth0的即为根而叶子节点可以用“没有任何子节点引用了这个id”来筛。这样综合查询的价值在于一步到位把全树的结构摸清根、叶、路径、层级全都有了。后面要做权限继承、数据汇总、报表钻取都是在这些字段上做文章。6. 常见问题与调试技巧实录6.1 死循环导致查询卡死递归最怕的就是无限循环。我遇到过几次数据里有个节点的parent_id指向了自己或者两个节点互相引用比如A的父亲是BB的父亲是A。这种循环图在递归CTE里会被无限迭代下去数据库永远查不完直到超时或者内存爆掉。解决办法有这么几个一个是给递归CTE加一个深度限制比如WHERE depth 20强制它跳出去。另一个是在锚点或者递归里排除掉已经走过的ID用path数组来判断“当前节点是否已经在路径里”。还有一种是在数据入口做校验禁止循环引用。我个人的习惯是开发阶段先在递归里带上深度限制和路径跑一遍看有没有异常循环确认没问题再优化掉。6.2 性能慢但数据量并不大每次递归迭代都是一次表扫描和连接操作如果每层的查找都没用上索引那性能会成倍恶化。比如parent_id没有索引的情况下树有十层、每层一百个节点你就要做十次全表扫描数据量大一点就卡得要命。我的调优经验是给parent_id建一个普通索引让JOIN条件能够走索引查找。还有一个技巧是在锚点查询时就尽量缩小根的范围从顶层开始就只挑你要的那棵子树而不是全表所有根都跑一遍。另外递归CTE每轮都会把结果物化存起来如果中间结果集特别大内存和临时表空间都会吃紧这时可以考虑分批查询或者理清业务是否真的需要全树。6.3 数据重复与去重陷阱UNION ALL不会去重如果递归成员里因为JOIN条件不严谨可能出现同一行被多次返回的情况。尤其是当parent_id存在重复引用比如一个节点有多个父亲时结果集就会翻倍。严格来说这是数据模型的问题但查询层面也得防。我的建议是递归成员里除了JOIN父子关系尽量带上唯一的深度条件或者路径限制让每个节点只出现一次。如果确实需要去重再在外层套DISTINCT但DISTINCT在高基数字段上也比较耗资源。更好的办法是把递归的范围收窄从根节点序列化路径下去避免同一个节点被两个不同的父节点引到。6.4 根节点和叶子节点的统计口径不一致统计根节点和统计叶子节点看似简单但口径不同会导致数字对不上。比如有的系统把根节点parent_id设为0但0本身不是真实存在的ID那么NOT EXISTS反查的时候就不会把0当成引用根节点查询却会用到0的条件两边统计口径就岔了。我的做法是建一个清晰的约定根节点parent_id一律用NULL子节点parent_id必须是存在的ID不允许有悬空引用和自引用。如果历史数据没法改那就在查询里统一加一个前置清洗逻辑先把不符合规则的数据剔除或者修复再让递归跑起来。这样统计出来的根和叶才可靠后面做报表才不会对不上。6.5 调试递归查询的几个实用技巧调试递归CTE我用的最多的方式是分段验证。先把锚点单独跑出来看看根节点谁对了没有再加上递归成员跑一遍看第一层出来的子节点对不对然后逐步放开深度限制一层一层确认。PostgreSQL里可以用max_depth这个参数配合EXPLAIN ANALYZE看每轮扫描情况非常直观。还有一个技巧是在递归结果里加一个“当前轮的批次号”就是看这一行是哪一轮迭代被查出来的。实现方式很简单锚点里设置batch0递归里batch1最后查询时就能看到数据是第几层的。有了这个判断递归是否按预期深度进行就一目了然。调试完毕后再把这种辅助字段去掉保持最终查询的干净。7. 写在最后一点个人经验递归查询这门手艺我用了好几年越用越觉得它像一把双刃剑。用得好处理树形数据手到擒来用得不好性能和坑位能让你怀疑人生。就“最上手信息”和“最下手信息”这两个需求来说我的建议永远是能不用递归就不用递归直接在原始表上用WHERE或者NOT EXISTS解决简单又高效。递归的真正价值是在需要整棵树的路径、层级、聚合时才会显现。我记得有一次做一个权限树的需求数据有几千个节点、七八层一开始用递归CTE全量拉出来结果每次都要好几秒后来改成先只查根和叶再按需展开中间层性能直接提升了一个量级。这种经验说给新人听他们总觉得多此一举但真到了复杂项目里就明白“够用就好”这四个字的分量了。如果你刚接触递归查询别怕报错也别嫌麻烦花一晚上的时间把一棵小树的根、叶、路径、深度全跑一遍所有逻辑就通了。以后再碰到各种花式的层级数据需求心里就有底了。