MySQL 递归 CTE（公用表表达式）

MySQL Recursive 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`：为子查询生成的列指定的名称。

• “子查询”：使用“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 子句中提供的条件为 true。

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

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

示例 1

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

id	姓名	工资
1	约翰	50000
2	简	60000
3	鲍勃	70000
4	爱丽丝	80000
5	迈克尔	90000
6	莎拉	100000
7	大卫	110000 芝麻乐开源众筹cms系统 芝麻乐开源众筹系统采用php+mysql开发，基于MVC开发，适用于各类互联网金融公司使用，程序具备模板分离技术，您可以根据您的需要进行应用扩展来达到更加强大功能。前端使用pintuer、jquery、layer等....系统易于使用和扩展简单的安装和升级向导多重业务逻辑判断，预防出现bug后台图表数据方式，一目了然后台包含但不限于以下功能：用户认证角色管理节点管理管理员管理上传配置支付配置短信平 下载
8	艾米丽	120000
9	标记	130000
10	朱莉娅	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。它通过将“员工”表与 CTE 本身递归连接来计算每个员工的任期。递归的基本情况从“员工”表中选择所有员工，起始任期为 0。递归情况将每个员工与 CTE 连接起来，并将其任期增加 1。

生成的“employee_tenure”CTE 包含“id”、“name”、“salary”、“hire_date”和“tenure”列。然后我们选择任期至少2年的员工的平均工资。它使用一个带有 WHERE 子句的简单 SELECT 语句来过滤掉任期小于 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时，条件变为假，递归终止。

结论

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