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_nameUNION [ALL, DISTINCT]SELECT col1, col2, ..., colN FROM cte_nameWHERE 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
爱图表
AI驱动的智能化图表创作平台
99 查看详情 
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 = 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
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 Recursive CTE 是一种递归定义的表达式，在分层数据、图形遍历、数据聚合和数据报告中很有用。递归 CTE 使用自己的名称引用自身，并且必须有终止条件。定义递归 CTE 的语法涉及使用WITH RECURSIVE 子句以及非递归和递归子查询。在本文中，我们讨论了递归 CTE 的语法和示例，包括使用递归 CTE 查找在公司工作至少 2 年的员工的平均工资，并生成一系列前 5 个奇数。总的来说，Recursive CTE是一个强大的工具，可以帮助用户在MySQL中编写复杂的查询。
以上就是MySQL 递归 CTE（公用表表达式）的详细内容，更多请关注创想鸟其它相关文章！
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
如发现本站有涉嫌抄袭侵权/违法违规的内容， 请发送邮件至 chuangxiangniao@163.com 举报，一经查实，本站将立刻删除。
发布者：程序猿，转转请注明出处：https://www.chuangxiangniao.com/p/317926.html
微信扫一扫 
支付宝扫一扫 
