SQL递归查询通过WITH RECURSIVE实现，用于处理层级或图结构数据，如组织架构、物料清单等；它由锚点成员和递归成员组成，利用UNION ALL连接，逐层遍历直到无新数据生成，适用于深度不确定的父子关系查询，但需注意性能、索引、循环引用及数据库递归深度限制等问题。
SQL的递归查询，说白了，就是一种处理层级或图形结构数据的强大工具。它允许我们通过一个初始的“起点”（称为锚点成员）和一套“规则”（递归成员），不断地迭代查询，直到满足某个终止条件。
WITH RECURSIVE
是SQL标准中实现这种递归查询的方式，它特别擅长解决那些传统连接操作搞不定的，深度不确定的层级关系问题，比如组织架构、物料清单或者社交网络中的关系链。
解决方案
在我看来，理解
WITH RECURSIVE
的关键在于它模拟了一种“自我循环”的思考模式。我们不再需要写一堆层层嵌套的
JOIN
来找出所有下属，或者计算一个产品的所有子组件，而是定义一个基础集，然后定义一个如何从当前结果集生成下一个结果集的规则。这种方式，不仅代码更简洁，也更能清晰地表达业务逻辑。
具体来说，一个
WITH RECURSIVE
CTE（Common Table Expression）通常包含两个主要部分：
锚点成员（Anchor Member）：这是递归的起始点，它是一个非递归的
SELECT
语句，用于建立初始的结果集。可以把它想象成你寻宝的第一个线索。递归成员（Recursive Member）：这是一个
SELECT
语句，它会引用CTE本身，并与某个表进行连接，从而从上一步的结果中生成新的行。这个成员会反复执行，直到不再有新的行产生。这就好像你拿着上一个线索找到下一个线索，如此循环。
这两个成员之间通常用
UNION ALL
（或
UNION
）连接起来。
UNION ALL
性能更好，因为它不检查重复项，但在某些情况下，如果你需要去重，
UNION
会是更好的选择。整个过程就像一个自动化的“深度优先”或“广度优先”遍历，直到所有相关的层级都被探索完毕。
什么时候应该考虑使用SQL递归查询？
在我个人的经验里，每当我遇到数据之间存在“父子关系”或“A依赖B，B又依赖C”这样的链式结构，并且这种链条的深度是不确定的，我就会立刻想到
WITH RECURSIVE
。
最典型的应用场景包括：
组织架构或员工层级：找出某个经理的所有下属，无论他们隔了多少层。这是我最常遇到的场景，传统方法需要写多个
LEFT JOIN
，既笨重又无法处理无限层级。物料清单（Bill of Materials, BOM）：一个产品由哪些组件构成，这些组件又由哪些子组件构成，直到最基本的原材料。这在制造业中非常常见。文件系统或目录结构：显示某个目录下所有的子目录和文件。社交网络中的关系链：比如查找“我的好友的好友”或者“与我相隔N度的人”。虽然对于大型社交网络，专门的图数据库可能更高效，但对于小规模分析，
WITH RECURSIVE
是个不错的选择。评论或论坛帖子：显示一个主帖下的所有回复及其子回复。
说白了，只要你的数据能画成一棵树或者一个有向图，并且你需要遍历这棵树或图的所有节点，
WITH RECURSIVE
就是你的得力助手。它让原本复杂的、需要应用程序逻辑来处理的问题，变得可以在数据库层面优雅地解决。
WITH RECURSIVE 具体怎么写？一个实战案例解析
我们用一个经典的员工层级结构来演示
WITH RECURSIVE
的写法。假设我们有一个
employees
表，包含
employee_id
、
employee_name
和
manager_id
，其中
manager_id
指向其上级员工的
employee_id
。
目标：找出所有直接或间接向“Alice”（假设她的
employee_id
是101）汇报的员工。
首先，我们模拟一下数据：
CREATE TABLE employees ( employee_id INT PRIMARY KEY, employee_name VARCHAR(50), manager_id INT);INSERT INTO employees (employee_id, employee_name, manager_id) VALUES(101, 'Alice', NULL), -- CEO(102, 'Bob', 101),(103, 'Charlie', 101),(104, 'David', 102),(105, 'Eve', 102),(106, 'Frank', 104),(107, 'Grace', 103);
现在，我们来写
WITH RECURSIVE
查询：
博思AIPPT
博思AIPPT来了，海量PPT模板任选，零基础也能快速用AI制作PPT。
117 查看详情
WITH RECURSIVE EmployeeHierarchy AS ( -- 锚点成员 (Anchor Member): 从Alice开始，她是我们的起点 SELECT e.employee_id, e.employee_name, e.manager_id, 0 AS level -- 标记层级，Alice是第0层 FROM employees e WHERE e.employee_id = 101 -- 指定起始员工ID UNION ALL -- 递归成员 (Recursive Member): 找到当前层级员工的所有下属 SELECT e.employee_id, e.employee_name, e.manager_id, eh.level + 1 AS level -- 下属的层级加1 FROM employees e JOIN EmployeeHierarchy eh ON e.manager_id = eh.employee_id -- 关键：连接到CTE本身)SELECT employee_id, employee_name, manager_id, levelFROM EmployeeHierarchyORDER BY level, employee_id;
代码解析：
WITH RECURSIVE EmployeeHierarchy AS (...)
: 定义了一个名为
EmployeeHierarchy
的递归CTE。锚点成员：
SELECT e.employee_id, e.employee_name, e.manager_id, 0 AS level FROM employees e WHERE e.employee_id = 101
这部分选出了Alice（ID为101）作为起始点，并给她标记为
level 0
。这是递归的基石。
UNION ALL
: 将锚点成员和递归成员的结果合并。递归成员：
SELECT e.employee_id, e.employee_name, e.manager_id, eh.level + 1 AS level FROM employees e JOIN EmployeeHierarchy eh ON e.manager_id = eh.employee_id
这里是核心。它将
employees
表（别名
e
）与我们正在构建的
EmployeeHierarchy
CTE（别名
eh
）进行连接。连接条件
e.manager_id = eh.employee_id
的意思是：找出所有其
manager_id
等于
EmployeeHierarchy
中某个
employee_id
的员工。这实际上就是在找当前层级员工的直接下属。
eh.level + 1 AS level
：每找到一层下属，我们就把他们的层级加1，这样我们就能追踪到他们在组织架构中的深度。
这个查询会首先把Alice放进
EmployeeHierarchy
。然后，在第一次递归中，它会找到所有
manager_id
是101的员工（Bob和Charlie），把他们加入
EmployeeHierarchy
，并标记为
level 1
。接着，在第二次递归中，它会找到所有
manager_id
是Bob或Charlie的员工（David, Eve, Grace），标记为
level 2
。这个过程会一直重复，直到再也找不到新的下属为止。最终，
EmployeeHierarchy
CTE会包含所有从Alice开始的层级结构。
使用WITH RECURSIVE的性能考量与注意事项
虽然
WITH RECURSIVE
非常强大，但它并非没有代价。在实际应用中，我发现有几个方面需要特别注意：
性能问题：
深度与广度：如果你的层级结构非常深或者每一层都有大量的节点（广度很大），递归查询的性能可能会急剧下降。每次递归都需要进行一次连接操作，数据量越大，开销越大。索引：确保用于连接的列（在这个例子中是
employee_id
和
manager_id
）有合适的索引。没有索引，数据库可能需要进行全表扫描，这会是灾难性的。数据量：对于非常庞大的数据集，或者需要频繁执行的复杂递归查询，可能需要考虑在应用层处理，或者使用专门的图数据库（如Neo4j）来获得更好的性能。
无限循环（Cycles）：
这是递归查询最常见的陷阱之一。如果你的数据中存在循环引用（比如A的经理是B，B的经理是C，而C的经理又是A），递归查询就会陷入无限循环，最终导致数据库报错（通常是“递归深度超出限制”）。如何避免：在设计数据模型时，应尽量避免循环引用。如果业务上允许，你可能需要在递归成员中加入额外的条件来检测和中断循环。一些数据库（如PostgreSQL）提供了
CYCLE
子句来帮助检测和处理循环。在我们的例子中，如果Bob的经理是Alice，而Alice的经理又是Bob，就会出现循环。一种手动处理方式是，在CTE中追踪已经访问过的路径，如果新节点已经在路径中，则停止。
UNION ALL
vs
UNION
：
大多数情况下，使用
UNION ALL
就足够了，因为它不进行去重，性能更好。但如果你的递归路径可能导致重复的节点（例如，一个节点可以通过多条路径到达），并且你只关心唯一的节点列表，那么
UNION
可能是必要的，尽管它会带来额外的去重开销。
MAXRECURSION
限制：
一些数据库系统（比如SQL Server）默认对递归查询的深度有限制（通常是100层）。如果你的层级深度可能超过这个限制，你需要通过
OPTION (MAXRECURSION N)
来显式设置一个更高的值，或者设置为0表示无限制（但要小心无限循环）。PostgreSQL和MySQL通常没有这个默认限制。
可读性与调试：
虽然
WITH RECURSIVE
比多层
JOIN
更简洁，但对于初学者来说，理解其执行流程可能需要一点时间。在调试时，可以先单独运行锚点成员，再逐步理解递归成员如何工作。
总的来说，
WITH RECURSIVE
是SQL工具箱里一把锋利的瑞士军刀，用好了能大大提高效率和代码的优雅度。但就像所有强大的工具一样，也需要我们理解其工作原理和潜在的风险，才能真正发挥它的价值。
以上就是什么是SQL的递归查询？WITH RECURSIVE的用法与场景的详细内容，更多请关注创想鸟其它相关文章！
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
如发现本站有涉嫌抄袭侵权/违法违规的内容， 请发送邮件至 chuangxiangniao@163.com 举报，一经查实，本站将立刻删除。
发布者：程序猿，转转请注明出处：https://www.chuangxiangniao.com/p/961960.html
微信扫一扫 
支付宝扫一扫 
