MySQL 递归 CTE(通用表表达式)

MySQL 递归 CTE 允许用户编写涉及递归操作的查询。递归 CTE 是一种递归定义的表达式。它在分层数据、图形遍历、数据聚合和数据报告中很有用。在本文中，我们将讨论递归 CTE 及其语法和示例。

简介

通用表表达式 (CTE) 是一种为 MySQL 中每个查询生成的临时结果集命名的方法。WITH 子句用于定义 CTE，使用此子句可以在单个语句中定义多个 CTE。但是，CTE 只能引用在同一个 WITH 子句中先前定义的其他 CTE。每个 CTE 的范围仅限于定义它的语句。

递归 CTE 是一种使用自己的名称引用自身的子查询。要定义递归 CTE，请使用 WITH RECURSIVE 子句，并且它必须具有终止条件。递归 CTE 通常用于生成序列和遍历分层或树结构数据。

语法

在 MySQL 中定义递归 CTE 的语法如下:

WITH RECURSIVE cte_name [(col1, col2, ...)]
AS (subquery)
SELECT col1, col2, ... FROM cte_name;

• `cte_name`:子查询块中写入的递归子查询的名称。

• `col1, col2, ..., colN`:子查询生成的列的名称。

• `subquery`:使用`cte_name`作为其自身名称来引用自身的 MySQL 查询。SELECT 语句中给出的列名应与`cte_name`后面的列表中提供的名称相匹配。

子查询块中提供的递归 CTE 结构

SELECT col1, col2, ..., colN FROM table_name
UNION [ALL, DISTINCT]
SELECT col1, col2, ..., colN FROM cte_name
WHERE clause

递归 CTE 具有非递归子查询，然后是递归子查询。

• 第一个 SELECT 语句是非递归语句。它为结果集提供初始行。

• `UNION [ALL, DISTINCT]` 用于向先前的结果集添加其他行。使用 `ALL` 和 `DISTINCT` 关键字用于在最后一个结果集中添加或删除重复行。

• 第二个 SELECT 语句是递归语句。它迭代地生成结果集，直到 WHERE 子句中提供的条件为真。

• 每次迭代生成的结果集都以上一次迭代生成的结果集作为基表。

• 当递归 SELECT 语句不生成任何其他行时，递归结束。

示例 1

考虑一个名为"employees"的表。它有列"id"、"name"和"salary"。找出在公司工作至少 2 年的员工的平均工资。"employees"表具有以下值:

因此，所需的查询如下所示

WITH RECURSIVE employee_tenure AS (
   SELECT id, name, salary, hire_date, 0 AS tenure
   FROM employees
   UNION ALL
   SELECT e.id, e.name, e.salary, e.hire_date, et.tenure + 1
   FROM employees e
   JOIN employee_tenure et ON e.id = et.id
   WHERE et.hire_date < DATE_SUB(NOW(), INTERVAL 2 YEAR)
)
SELECT AVG(salary) AS average_salary
FROM employee_tenure
WHERE tenure >= 2;

在此查询中，我们首先定义一个名为"employee_tenure"的递归 CTE。它通过递归地将"employees"表与 CTE 本身连接起来，计算每个员工的任职年限。递归的基本情况是从"employees"表中选择所有起始任职年限为 0 的员工。递归情况将每个员工与 CTE 连接起来，并将其任职年限增加 1。

生成的"employee_tenure"CTE 包含"id"、"name"、"salary"、"hire_date"和"tenure"列。然后，我们选择任职年限至少为 2 年的员工的平均工资。它使用一个简单的 SELECT 语句和一个 WHERE 子句来过滤掉任职年限少于 2 年的员工。

查询的输出将是一行。它将包含在公司工作至少 2 年的员工的平均工资。具体值将取决于"员工"表中分配给每个员工的随机工资。

示例 2

以下是在 MySQL 中使用递归 CTE 生成一系列前 5 个奇数的示例:

查询

WITH RECURSIVE 
odd_no (sr_no, n) AS
(
   SELECT 1, 1 
   UNION ALL
   SELECT sr_no+1, n+2 FROM odd_no WHERE sr_no < 5 
)
SELECT * FROM odd_no;  

输出

上述查询由两部分组成 - 非递归和递归。

非递归部分 - 它将生成由两列(分别名为"sr_no"和"n")和一行组成的初始行。

查询

SELECT 1, 1

输出

递归部分 - 它将向先前的输出添加行，直到满足终止条件，在本例中是当 sr_no 小于 5 时。

SELECT sr_no+1, n+2 FROM odd_no WHERE sr_no < 5

当 `sr_no` 变为 5 时，条件变为 false，并且递归终止。

结论

MySQL 递归 CTE 是一种递归定义的表达式，在分层数据、图形遍历、数据聚合和数据报告中很有用。递归 CTE 使用自己的名称引用自身，并且必须具有终止条件。定义递归 CTE 的语法涉及使用 WITH RECURSIVE 子句以及非递归和递归的子查询。在本文中，我们讨论了递归 CTE 的语法和示例，包括使用递归 CTE 查找在公司工作至少 2 年的员工的平均工资，并生成前 5 个奇数的序列。总的来说，递归 CTE 是一个强大的工具，可以帮助用户在 MySQL 中编写复杂的查询。

相关文章

• MySQL 递归 CTE(通用表表达式)