迹忆客 专注技术分享

在本指南中，我们将了解 MySQL 的递归查询。 如何在 SQL 中编写递归查询及其工作原理将在本指南中进行解释，以便您更好地理解。

MySQL 递归查询

SQL中的递归查询是子查询； 顾名思义，它以递归方式工作。 它有一个基本案例、一个用户定义的名称和一个带有终止条件的递归案例。

with [Recursive] CTE(user_defined name) AS
(
    SELECT query (Non Recursive query or the Base query)
    UNION
    SELECT query (recursive query using the name [with a termination condition])
)
SELECT * from CTE_name;

以上是递归SQL查询的伪代码。 让我们更深入地研究它。

with 从句

with [Recursive] CTE(user_defined name) AS

SQL 的with 子句在开头使用，与SQL 的Recursive 关键字一起使用。 然后使用 AS 关键字，后跟 CTE，一个用户定义的名称。

这种关键字语法构成了我们查询的基本情况。

CTE 称为公用表表达式，是用户定义的临时命名结果集，稍后在后续的 SELECT 语句中使用。

基本查询

SELECT query (Non Recursive query or the Base query)

这是称为基本查询的两个查询中的第一个。 这是一个非递归查询，这里将根据递归发生的基础输入提供基本输入。

UNION 语句

UNION

中间使用 Union 运算符合并我们的第一个和第二个查询。

递归查询

SELECT query (recursive query using the name [with a termination condition])

这是递归查询，我们需要提供之前使用 with 子句创建的 CTE 表，并提供终止条件，当它变为真时，将终止递归。

Base和Recursive query写在括号()里面，如上面的伪代码所示。

SELECT * from CTE_name

此查询将查看使用此递归技术创建的表。

在 MySQL 中实现递归查询

让我们通过正确地实现这个查询来理解它的工作原理。

with recursive number_printer AS
(
    SELECT 1 AS digit
    UNION
    SELECT digit+1 FROM number_printer WHERE digit<5
)
SELECT * FROM number_printer

上表是我们程序执行的结果。 这是它的执行方式。

查询开始使用 with 子句和递归关键字，我们使用 print_number 作为我们的 CTE。 一旦我们执行代码，程序就会寻找一个基本案例，因为它知道这是一个递归查询。

在base query中，我们从1开始base case，这里使用的别名是digit。 在执行的第一次迭代中，输出将是从基本查询返回的结果。

初始记录将是基本查询的输出。

从基本查询返回的结果将成为递归查询的输入。 在第二次迭代中，递归查询使用前一个查询的输出数据并检查终止条件。

如果满足终止条件，则迭代停止； 否则进入第三次迭代。 第三次迭代使用第二次迭代返回的输出作为输入。

这是递归发生的唯一逻辑。

执行第三次迭代，并将此迭代的结果输出用作第四次迭代的输入。 第四次迭代然后将基本查询的输出与其输入相加。

它一直递归发生，直到满足终止条件。

该表，即 number_printer，在这里非常重要。 必须使用该表来使此查询递归。 对于查询的完成，使用 WHERE 子句编写终止条件。

现在，让我们再举一个关于递归查询的例子。

示例 2：层次结构

假设我们有一个具有某种层次结构的组织。 最高层有一名经理，两名经理受该经理的直接监督，而这两名经理中的每一位都有一名直接受其监督的经理。

现在，我们已经创建了一个名为 network 的表，其中包含 id、name 和 ManagerID 等列。 它们分别定义了员工的 ID、姓名和经理 ID。

如下表所示：

在上表中，我们获得了员工的姓名和经理 ID，但我们希望根据我们之前讨论的层次结构为这些经理正确分配等级。 因此，我们使用以下代码：

WITH RECURSIVE hierarchy AS
( SELECT id, name, 1 AS level FROM network WHERE id = 1
UNION
SELECT n.id, n.name, h.level+1 FROM hierarchy h
JOIN network n on h.id = n.ManagerID
)
SELECT * FROM hierarchy

上面的递归查询代码将如我们所愿地解决问题。 让我们深入了解这段代码的作用。

With 子句根据语法与 Recursive 关键字一起使用，这里的 CTE 命名为层次结构。

在基本查询中，它从网络表中选择 id 和 name，并获取 id 等于 1 的值。然后，它将其值 AS level 设置为整数 1。

此处使用级别来展示经理在层次结构中的位置。

我们完成了基本查询； 现在，代码使用 UNION 子句将基本查询与递归查询合并。

递归查询从网络表中选择 id 和 name。 这里的 n 是网络表的别名，这个点 (.) 在这里的作用是限制要从我们在它之前作为输入提供的特定表中获取的值。

h 是层次表的别名，子句 level+1 确保从层次表中获取的整数值加 1。

这部分递归查询从network表中取name和id，整数值来自hierarchy，对其进行加1，输出保存在level中。 此查询更新并定义经理的级别。

在查询的下一部分中，使用了 JOIN 子句，默认情况下它作为内部连接运行。 在这里，网络表中的 ManagerID 与层次结构表中的 ID 相匹配，并与上述查询进行内部连接。

逻辑是，当经理 ID 与 ID 匹配时，通过将经理的级别加 1 来分配该实体的级别。 这样，监督的排名就分配好了。