MySQL 遞迴 CTE（公共表表達式）

---

MySQL 遞迴 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`：賦予子查詢生成的列的名稱。

• `subquery`：一個 MySQL 查詢，它使用 `cte_name` 作為自己的名稱引用自身。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 子句中提供的條件為真。

• 每次迭代產生的結果集都將前一次迭代產生的結果集作為基表。

• 當遞迴 SELECT 語句不再產生任何其他行時，遞迴結束。

示例 1

考慮一個名為“employees”的表。它包含列“id”、“name”和“salary”。查詢在公司工作至少 2 年的員工的平均工資。“employees”表具有以下值

id	姓名	工資
1	John	50000
2	Jane	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。它透過將“employees”表與 CTE 本身遞迴聯接來計算每個員工的任期。遞迴的基本情況從“employees”表中選擇所有員工，起始任期為 0。遞迴情況將每個員工與 CTE 聯接並將其任期增加 1。

生成的“employee_tenure”CTE 包含列“id”、“name”、“salary”、“hire_date”和“tenure”。然後，我們選擇任期至少為 2 年的員工的平均工資。它使用一個簡單的 SELECT 語句和一個 WHERE 子句，該子句過濾掉任期少於 2 年的員工。

查詢的輸出將是一行。它將包含在公司工作至少 2 年的員工的平均工資。具體值將取決於分配給“employees”表中每個員工的隨機工資。

示例 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 遞迴 CTE 是一個遞迴定義的表示式，在層次資料、圖遍歷、資料聚合和資料報告中很有用。遞迴 CTE 使用自己的名稱引用自身，並且必須具有終止條件。定義遞迴 CTE 的語法涉及使用 WITH RECURSIVE 子句和非遞迴和遞迴的子查詢。在本文中，我們討論了遞迴 CTE 及其語法和示例，包括使用遞迴 CTE 查詢在公司工作至少 2 年的員工的平均工資，以及生成前 5 個奇數序列。總的來說，遞迴 CTE 是一種強大的工具，可以幫助使用者在 MySQL 中編寫複雜的查詢。