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