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（公用表表達式）的詳細內容，更多請關注www.92cms.cn其它相關文章！
