简介：在本教程中，您将学习MySQL递归CTE以及如何使用它遍历分层数据。

请注意，公用表表达式或CTE仅在MySQL 8.0或更高版本中可用。
因此，您应该安装正确的MySQL版本才能使用本教程中的语句。

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结构基本结果集的初始查询。
  初始查询部分称为锚成员。

• 递归查询部分是引用CTE名称的查询，因此，它称为递归成员。
  递归成员通过UNION ALL或UNION DISTINCT运算符与锚定成员相连。

• 当递归成员不返回任何行时，确保递归停止的终止条件。

递归CTE的执行顺序如下：

• 首先，将成员分为两个：锚定成员和递归成员。

• 接下来，执行锚成员以形成基本结果集(R0)，并将此基本结果集用于下一次迭代。

• 然后，以Ri结果集作为输入执行递归成员，并使Ri + 1作为输出。

• 之后，重复第三步，直到递归成员返回空结果集，换句话说，满足终止条件。

• 最后，使用UNION ALL运算符组合从R0到Rn的结果集。

递归成员限制

递归成员不得包含以下构造：

• 汇总函数，例如MAX，MIN，SUM，AVG，COUNT等
• GROUP BY子句
• ORDER BY子句
• LIMIT子句
• 不同

注意，以上约束不适用于锚固构件。
同样，仅当您使用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的元素：

递归CTE返回以下输出：

递归CTE的执行步骤如下：

• 首先，将锚点成员和递归成员分开。

• 接下来，锚成员形成初始行(SELECT 1)，因此第一次迭代将生成1 + 1 = 2，其中n = 1。

• 然后，第二次迭代在第一次迭代(2)的输出上进行操作，并产生2 + 1 = 3(n = 2)。

• 之后，在第三次操作(n = 3)之前，已满足终止条件(n <3)，因此查询停止。

• 最后，使用UNION ALL运算符组合所有结果集1、2和3

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

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

员工表具有reportTo列，该列引用employeeNumber列。
reportTo列存储经理的ID。
最高管理者不会向公司组织结构中的任何人报告，因此，reportsTo列中的值为NULL。

您可以应用递归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

此查询(锚成员)返回最高报告者为NULL的最高经理。

其次，通过引用CTE名称(在这种情况下为employee_paths)来创建递归成员：

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

该查询(递归成员)返回经理的所有直接报告，直到没有其他直接报告为止。
如果递归成员不返回任何直接报告，则递归停止。

第三，使用employee_paths CTE的查询将CTE返回的结果集与offices表连接起来以构成最终结果集。

以下是查询的输出：

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