17370845950

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

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

查询：

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