什么是SQL的递归查询？WITH RECURSIVE的用法与场景（递归.用法.场景.查询.SQL...）

wufei123 发布于 2025-09-11 阅读（6）
SQL递归查询通过WITH RECURSIVE实现，用于处理层级或图结构数据，如组织架构、物料清单等；它由锚点成员和递归成员组成，利用UNION ALL连接，逐层遍历直到无新数据生成，适用于深度不确定的父子关系查询，但需注意性能、索引、循环引用及数据库递归深度限制等问题。

什么是sql的递归查询？with recursive的用法与场景

SQL的递归查询，说白了，就是一种处理层级或图形结构数据的强大工具。它允许我们通过一个初始的“起点”（称为锚点成员）和一套“规则”（递归成员），不断地迭代查询，直到满足某个终止条件。

WITH RECURSIVE
是SQL标准中实现这种递归查询的方式，它特别擅长解决那些传统连接操作搞不定的，深度不确定的层级关系问题，比如组织架构、物料清单或者社交网络中的关系链。 解决方案

在我看来，理解

WITH RECURSIVE
的关键在于它模拟了一种“自我循环”的思考模式。我们不再需要写一堆层层嵌套的
JOIN
来找出所有下属，或者计算一个产品的所有子组件，而是定义一个基础集，然后定义一个如何从当前结果集生成下一个结果集的规则。这种方式，不仅代码更简洁，也更能清晰地表达业务逻辑。

具体来说，一个

WITH RECURSIVE
CTE（Common Table Expression）通常包含两个主要部分：
  1. 锚点成员（Anchor Member）：这是递归的起始点，它是一个非递归的
    SELECT
    语句，用于建立初始的结果集。可以把它想象成你寻宝的第一个线索。
  2. 递归成员（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
查询： PIA PIA

全面的AI聚合平台，一站式访问所有顶级AI模型

PIA226 查看详情 PIA 
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,
    level
FROM
    EmployeeHierarchy
ORDER BY
    level, employee_id;

代码解析：

  1. WITH RECURSIVE EmployeeHierarchy AS (...)
    : 定义了一个名为
    EmployeeHierarchy
    的递归CTE。
  2. 锚点成员：
    • 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
      。这是递归的基石。
  3. UNION ALL
    : 将锚点成员和递归成员的结果合并。
  4. 递归成员：
    • 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
非常强大，但它并非没有代价。在实际应用中，我发现有几个方面需要特别注意：

  1. 性能问题：

    • 深度与广度：如果你的层级结构非常深或者每一层都有大量的节点（广度很大），递归查询的性能可能会急剧下降。每次递归都需要进行一次连接操作，数据量越大，开销越大。
    • 索引：确保用于连接的列（在这个例子中是
      employee_id
      和
      manager_id
      ）有合适的索引。没有索引，数据库可能需要进行全表扫描，这会是灾难性的。
    • 数据量：对于非常庞大的数据集，或者需要频繁执行的复杂递归查询，可能需要考虑在应用层处理，或者使用专门的图数据库（如Neo4j）来获得更好的性能。

  2. 无限循环（Cycles）：

    • 这是递归查询最常见的陷阱之一。如果你的数据中存在循环引用（比如A的经理是B，B的经理是C，而C的经理又是A），递归查询就会陷入无限循环，最终导致数据库报错（通常是“递归深度超出限制”）。
    • 如何避免：在设计数据模型时，应尽量避免循环引用。如果业务上允许，你可能需要在递归成员中加入额外的条件来检测和中断循环。一些数据库（如PostgreSQL）提供了
      CYCLE
      子句来帮助检测和处理循环。在我们的例子中，如果Bob的经理是Alice，而Alice的经理又是Bob，就会出现循环。一种手动处理方式是，在CTE中追踪已经访问过的路径，如果新节点已经在路径中，则停止。

  3. UNION ALL
    vs 
    UNION
    ：
    • 大多数情况下，使用
      UNION ALL
      就足够了，因为它不进行去重，性能更好。
    • 但如果你的递归路径可能导致重复的节点（例如，一个节点可以通过多条路径到达），并且你只关心唯一的节点列表，那么
      UNION
      可能是必要的，尽管它会带来额外的去重开销。

  4. MAXRECURSION
    限制：
    • 一些数据库系统（比如SQL Server）默认对递归查询的深度有限制（通常是100层）。如果你的层级深度可能超过这个限制，你需要通过
      OPTION (MAXRECURSION N)
      来显式设置一个更高的值，或者设置为0表示无限制（但要小心无限循环）。PostgreSQL和MySQL通常没有这个默认限制。

  5. 可读性与调试：

    • 虽然
      WITH RECURSIVE
      比多层
      JOIN
      更简洁，但对于初学者来说，理解其执行流程可能需要一点时间。在调试时，可以先单独运行锚点成员，再逐步理解递归成员如何工作。

总的来说，

WITH RECURSIVE
是SQL工具箱里一把锋利的瑞士军刀，用好了能大大提高效率和代码的优雅度。但就像所有强大的工具一样，也需要我们理解其工作原理和潜在的风险，才能真正发挥它的价值。

以上就是什么是SQL的递归查询？WITH RECURSIVE的用法与场景的详细内容，更多请关注知识资源分享宝库其它相关文章！

相关标签： mysql 工具 社交网络 sql mysql 架构 select union 递归 循环 堆 bom table postgresql 数据库 neo4j 自动化 大家都在看： SQL临时表存储聚合结果怎么做_SQL临时表存储聚合数据方法 Oracle数据源连接泄露防范_Oracle数据源连接泄漏预防措施 Oracle透明数据源怎么配置_Oracle透明数据源建立方法解析 SQLAVG函数计算时如何保留小数_SQLAVG函数保留小数位方法 SQL查询速度慢如何优化_复杂SQL查询性能优化十大方法

标签:  递归 用法 场景 

相关阅读

发表评论:

◎欢迎参与讨论，请在这里发表您的看法、交流您的观点。

最近发表

标签列表