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`：一个 MySQL 查询，它使用 `cte_name` 作为自己的名称引用自身。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”表具有以下值

id	姓名	工资
1	John	50000
2	Jane	60000
3	Bob	70000
4	Alice	80000
5	Michael	90000
6	Sarah	100000
7	David	110000
8	Emily	120000
9	Mark	130000
10	Julia	140000

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

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 年的员工的平均工资。具体值将取决于分配给“employees”表中每个员工的随机工资。

示例 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
1	1
2	3
3	5
4	7
5	9

上述查询包含两部分：非递归和递归。

非递归部分 - 它将生成初始行，这些行包含名为“sr_no”和“n”的两列以及一行。

查询

SELECT 1, 1

输出

sr_no	n
1	1

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

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

当 `sr_no` 变成 5 时，条件变为假，递归终止。

结论

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