MySQL Recursive CTE 允許用戶編寫涉及遞歸操作的查詢。遞歸 CTE 是遞歸定義的表達(dá)式。它在分層數(shù)據(jù)、圖形遍歷、數(shù)據(jù)聚合和數(shù)據(jù)報(bào)告中很有用。在本文中，我們將討論遞歸 CTE 及其語(yǔ)法和示例。
簡(jiǎn)介
公用表表達(dá)式（CTE）是一種為 MySQL 中每個(gè)查詢生成的臨時(shí)結(jié)果集命名的方法。 WITH 子句用于定義 CTE，并且可以使用該子句在單個(gè)語(yǔ)句中定義多個(gè) CTE。但是，CTE 只能引用先前在同一WITH 子句中定義的其他CTE。每個(gè) CTE 的范圍僅限于定義它的語(yǔ)句。
遞歸 CTE 是一種使用自己的名稱引用自身的子查詢。要定義遞歸CTE，需要使用WITH RECURSIVE 子句，并且它必須有終止條件。遞歸 CTE 通常用于生成序列和遍歷分層或樹結(jié)構(gòu)數(shù)據(jù)。
語(yǔ)法
MySQL中定義遞歸CTE的語(yǔ)法如下：
WITH RECURSIVE cte_name [(col1, col2, ...)] AS (subquery) SELECT col1, col2, ... FROM cte_name;
登錄后復(fù)制
`cte_name`：為子查詢塊中編寫的遞歸子查詢指定的名稱。
`col1, col2, …, colN`：為子查詢生成的列指定的名稱。
“子查詢”：使用“cte_name”作為自己的名稱來(lái)引用自身的 MySQL 查詢。 SELECT 語(yǔ)句中給出的列名稱應(yīng)與列表中提供的名稱相匹配，后跟“cte_name”。
子查詢塊中提供的遞歸CTE結(jié)構(gòu)
SELECT col1, col2, ..., colN FROM table_name UNION [ALL, DISTINCT] SELECT col1, col2, ..., colN FROM cte_name WHERE clause
登錄后復(fù)制
遞歸 CTE 具有非遞歸子查詢，然后是遞歸子查詢。
第一個(gè) SELECT 語(yǔ)句是非遞歸語(yǔ)句。它為結(jié)果集提供初始行。
`UNION [ALL, DISTINCT]` 用于將附加行添加到先前的結(jié)果集中。使用“ALL”和“DISTINCT”關(guān)鍵字用于添加或刪除最后一個(gè)結(jié)果集中的重復(fù)行。
第二個(gè) SELECT 語(yǔ)句是遞歸語(yǔ)句。它迭代地生成結(jié)果集，直到 WHERE 子句中提供的條件為 true。
每次迭代產(chǎn)生的結(jié)果集以上一次迭代產(chǎn)生的結(jié)果集為基表。
當(dāng)遞歸 SELECT 語(yǔ)句不生成任何其他行時(shí)，遞歸結(jié)束。
示例 1
考慮一個(gè)名為“employees”的表。它有“id”、“name”和“salary”列。查找在公司工作至少 2 年的員工的平均工資。 “employees”表具有以下值：
|
id |
姓名 |
工資 |
|---|---|---|
|
1 |
約翰 |
50000 |
|
2 |
簡(jiǎn) |
60000 |
|
3 |
鮑勃 |
70000 |
|
4 |
愛麗絲 |
80000 |
|
5 |
邁克爾 |
90000 |
|
6 |
莎拉 |
100000 |
|
7 |
大衛(wèi) |
110000 |
|
8 |
艾米麗 |
120000 |
|
9 |
標(biāo)記 |
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;
登錄后復(fù)制
在此查詢中，我們首先定義一個(gè)名為“employee_tenure”的遞歸 CTE。它通過將“員工”表與 CTE 本身遞歸連接來(lái)計(jì)算每個(gè)員工的任期。遞歸的基本情況從“員工”表中選擇所有員工，起始任期為 0。遞歸情況將每個(gè)員工與 CTE 連接起來(lái)，并將其任期增加 1。
生成的“employee_tenure”CTE 包含“id”、“name”、“salary”、“hire_date”和“tenure”列。然后我們選擇任期至少2年的員工的平均工資。它使用一個(gè)帶有 WHERE 子句的簡(jiǎn)單 SELECT 語(yǔ)句來(lái)過濾掉任期小于 2 的員工。
查詢的輸出將是一行。它將包含在公司工作至少 2 年的員工的平均工資。具體值取決于“員工”表中分配給每個(gè)員工的隨機(jī)工資。
示例 2
下面是在 MySQL 中使用遞歸 CTE 生成一系列前 5 個(gè)奇數(shù)的示例：
查詢
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;
登錄后復(fù)制
輸出
|
sr_no |
n |
|---|---|
|
1 |
1 |
|
2 |
3 |
|
3 |
5 |
|
4 |
7 |
|
5 |
9 |
上面的查詢由兩部分組成——非遞歸和遞歸。
非遞歸部分 – 它將生成由名為“sr_no”和“n”的兩列和一行組成的初始行。
查詢
SELECT 1, 1
登錄后復(fù)制
輸出
|
sr_no |
n |
|---|---|
|
1 |
1 |
遞歸部分 – 它將向先前的輸出添加行，直到滿足終止條件，在本例中是當(dāng) sr_no 小于 5 時(shí)。
SELECT sr_no+1, n+2 FROM odd_no WHERE sr_no < 5
登錄后復(fù)制
當(dāng)`sr_no`變?yōu)?時(shí)，條件變?yōu)榧伲f歸終止。
結(jié)論
MySQL Recursive CTE 是一種遞歸定義的表達(dá)式，在分層數(shù)據(jù)、圖形遍歷、數(shù)據(jù)聚合和數(shù)據(jù)報(bào)告中很有用。遞歸 CTE 使用自己的名稱引用自身，并且必須有終止條件。定義遞歸 CTE 的語(yǔ)法涉及使用WITH RECURSIVE 子句以及非遞歸和遞歸子查詢。在本文中，我們討論了遞歸 CTE 的語(yǔ)法和示例，包括使用遞歸 CTE 查找在公司工作至少 2 年的員工的平均工資，并生成一系列前 5 個(gè)奇數(shù)?？偟膩?lái)說，Recursive CTE是一個(gè)強(qiáng)大的工具，可以幫助用戶在MySQL中編寫復(fù)雜的查詢。
以上就是MySQL 遞歸 CTE（公用表表達(dá)式）的詳細(xì)內(nèi)容，更多請(qǐng)關(guān)注www.92cms.cn其它相關(guān)文章！
