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
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
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中文網其他相關文章！