MySQL递归CTE

时间:2019-11-20 08:52:06  来源:igfitidea点击:

简介:在本教程中,您将学习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以及如何使用它遍历分层数据。