Skip to main content

MySQL递归CTE的权威指南

MySQL递归CTE简介

递归公共表表达式(CTE)是一个CTE,它有一个子查询引用CTE名称本身。下面说明了递归CTE的语法

WITH RECURSIVE cte_name AS (
initial_query -- anchor member
UNION ALL
recursive_query -- recursive member that references to the CTE name
)
SELECT * FROM cte_name;

递归CTE由三个主要部分组成:

递归CTE的执行顺序如下:

递归成员限制

递归成员不能包含以下构造:

请注意,上述约束不适用于锚定构件。此外,仅当您使用UNION运算符时,对DISTINCT的禁止才适用。如果使用UNION DISTINCT运算符,则允许使用DISTINCT。

此外,递归成员只能在其FROM子句中引用CTE名称一次,而不能在任何子查询中引用。

简单的MySQL递归CTE示例

请参见以下简单的递归CTE示例:

WITH RECURSIVE cte_count (n) 
AS (
SELECT 1
UNION ALL
SELECT n + 1
FROM cte_count
WHERE n < 3
)
SELECT n
FROM cte_count;

在本例中,将执行以下查询:

SELECT 1

返回1作为基本结果集的锚定成员。

下面的查询

SELECT n + 1
FROM cte_count
WHERE n < 3

是递归成员,因为它引用了CTE的名称,即CTE_count。

递归成员中的表达式n<3是终止条件。一旦n等于3,递归成员返回一个空集,该空集将停止递归。

下图说明了上述CTE的要素:

MySQL Recursive CTE

递归CTE返回以下输出:

MySQL Recursive CTE Example

递归CTE的执行步骤如下:

使用MySQL递归CTE遍历分层数据

我们将使用classicmodels示例数据库中的employees表进行演示。

employees表的reportsTo列引用employeeNumber列。reportsTo列存储经理的ID。最高管理者不向公司组织结构中的任何人报告,因此reportsTo列中的值为空。

您可以使用递归CTE以自上而下的方式查询整个组织结构,如下所示:

WITH RECURSIVE employee_paths AS
( SELECT employeeNumber,
reportsTo managerNumber,
officeCode,
1 lvl
FROM employees
WHERE reportsTo IS NULL
UNION ALL
SELECT e.employeeNumber,
e.reportsTo,
e.officeCode,
lvl+1
FROM employees e
INNER JOIN employee_paths ep ON ep.employeeNumber = e.reportsTo )
SELECT employeeNumber,
managerNumber,
lvl,
city
FROM employee_paths ep
INNER JOIN offices o USING (officeCode)
ORDER BY lvl, city;

让我们将查询分解为更小的部分,以便更容易理解。

首先,使用以下查询形成锚定成员:

SELECT 
employeeNumber,
reportsTo managerNumber,
officeCode
FROM
employees
WHERE
reportsTo IS NULL

此查询(锚定成员)返回reportsTo为空的顶级经理。

其次,通过引用CTE名称来创建递归成员,在本例中是employee_path:

SELECT 
e.employeeNumber,
e.reportsTo,
e.officeCode
FROM
employees e
INNER JOIN employee_paths ep
ON ep.employeeNumber = e.reportsTo

此查询(递归成员)返回经理的所有直接下属,直到不再有直接下属为止。如果递归成员不返回直接报告,则递归停止。

第三,使用employee_paths CTE的查询将CTE返回的结果集与Office表连接起来,形成最终的结果集。

以下是查询的输出:

MySQL Recursive CTE Hierarchical Data Traversal

在本教程中,您学习了MySQL递归CTE,以及如何使用它遍历分层数据。