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 语句不生成任何其他行时,递归结束。
考虑一个名为“employees”的表。它有“id”、“name”和“salary”列。查找在公司工作至少 2 年的员工的平均工资。 “employees”表具有以下值:
id |
姓名 |
工资 |
---|---|---|
1 |
约翰 |
50000 |
2 |
简 |
60000 |
3 |
鲍勃 |
70000 |
4 |
爱丽丝 |
80000 |
5 |
迈克尔 |
90000 |
6 |
莎拉 |
100000 |
7 |
大卫 |
110000 |
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 年的员工的平均工资。具体值取决于“员工”表中分配给每个员工的随机工资。
下面是在 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(公用表表达式)的详细内容。更多信息请关注PHP中文网其他相关文章!