MySQL recursive CTE (common table expression)

MySQL Recursive CTE allows users to write queries involving recursive operations. A recursive CTE is an expression defined recursively. It is useful in hierarchical data, graph traversal, data aggregation and data reporting. In this article, we will discuss recursive CTE, its syntax and examples.

Introduction

A common table expression (CTE) is a way of naming the temporary result set generated by each query in MySQL. The WITH clause is used to define CTEs, and you can use this clause to define multiple CTEs in a single statement. However, a CTE can only refer to other CTEs previously defined in the same WITH clause. The scope of each CTE is limited to the statement that defines it.

A recursive CTE is a subquery that refers to itself by its own name. To define a recursive CTE, the WITH RECURSIVE clause needs to be used, and it must have a terminating condition. Recursive CTE is often used to generate sequences and traverse hierarchical or tree-structured data.

grammar

The syntax for defining recursive CTE in MySQL is as follows:

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

• `cte_name`: The name specified for the recursive subquery written in the subquery block.

• `col1, col2, ..., colN`: The names specified for the columns generated by the subquery.

• "Subquery": A MySQL query that refers to itself using "cte_name" as its name. The column name given in the SELECT statement should match the name provided in the list, followed by "cte_name".

Recursive CTE structure provided in subquery block

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

A recursive CTE has a non-recursive subquery followed by a recursive subquery.

• The first SELECT statement is a non-recursive statement. It provides the initial rows for the result set.

• `UNION [ALL, DISTINCT]` is used to add additional rows to the previous result set. Use the "ALL" and "DISTINCT" keywords to add or remove duplicate rows from the last result set.

• The second SELECT statement is a recursive statement. It iteratively builds the result set until the condition provided in the WHERE clause is true.

• The result set produced by each iteration is based on the result set produced by the previous iteration.

• Recursion ends when the recursive SELECT statement does not produce any additional rows.

Example 1

Consider a table named "employees". It has "id", "name" and "salary" columns. Find the average salary of employees who have been with the company for at least 2 years. The "employees" table has the following values:

id	Name	salary
1	John	50000
2	simple	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。它通过将“员工”表与 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中编写复杂的查询。

The above is the detailed content of MySQL recursive CTE (common table expression). For more information, please follow other related articles on the PHP Chinese website!