MySQL - MySQL 8.0(三)進階操作：WITH AS.公用表表達式（CTE）

文章目錄

• 非遞歸 CTE
• • 1. 派生表（子查詢）
  • 2. CTE
  • 3. CTE可以引用其他CTE
• 遞歸 CTE
• • 1. 文法解釋
  • 2. 舉個栗子

此學習文是基于MySQL 8.0寫的

得益于大神朋友的悉心指導解決不少坑，才寫出此文，向大神奉上膝蓋

  MySQL 8支援公用表表達式，包括

非遞歸

和

遞歸

兩種。

  公用表表達式允許使用命名的臨時結果集，這是通過允許在

SELECT

語句和某些其他語句前面使用

WITH

子句來實作的。

  不能在同一查詢中兩次引用

派生表（子查詢）

，因為那樣的話，查詢會根據派生表的引用次數計算兩次或多次，這會引發嚴重的性能問題。使用CTE後，子查詢隻會計算一次。

非遞歸 CTE

1. 派生表（子查詢）

2. CTE

with derived as (
	subquery
) 
select …… from derived, table_name ……;
           

  CTE可能在

SELECT/UPDATE/DELETE

之前，包括

with derived as ( subquery )

的子查詢， 例如：

with derived as ( 
	subquery 
) 
delete from table_name 
where table_name.col_name in (
	select col_name from derived
);
           

3. CTE可以引用其他CTE

with derived_one as (
	subquery
),
derived_two as (
	select …… from derived_one
)
select …… from derived_one, derived_two ……;
           

遞歸 CTE

1. 文法解釋

  遞歸CTE是一種特殊的CTE，其子查詢會引用自己的名字。

WITH

子句必須以

WITH RECURSIVE

開頭。遞歸CTE子查詢包括兩部分：

seed查詢

和

recursive查詢

，由

UNION[ALL]

或

UNION DISTINCT

分隔。

• seed select

  被執行一次以建立初始資料子集；

• recursive select

  被重複執行以傳回資料的子集，直到獲得完整的結果集。

  當疊代不會生成任何新行時，遞歸會停止。這對挖掘

  層次結構

  （父/子或部分/子部分）非常有用。

with recursive {temp_table_name}(n) as (
	select …… from table_name  /* "seed select" */
	union all 
	select …… from {temp_table_name}, table_name  /* "recursive select" */
)
select …… from derived ……;
           

假設要列印從 1 到 5 的所有數字：

mysql> with recursive derived(n) as (
    -> select 1  /* "seed select" */
    -> union all 
    -> select n + 1 from derived where n < 5  /* "recursive select" */
    -> )
    -> select * from derived;
+------+
| n    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+
5 rows in set (0.00 sec)
           

  在每次疊代中，

SELECT

都會生成一個帶有新值的行，比前一行的值 n 多 1。第一次疊代在初始行集合（ 1 ）上運作并生成值為 1 + 1 = 2 的行；第二次疊代對第一次疊代的行集合（ 2 ）進行操作并生成值為 2 + 1 = 3 的行。以此類推，一直持續到 n 不再小于 5 時，遞歸結束。

2. 舉個栗子

  假設要執行分層資料周遊，以便為每個員工生成一個組織結構圖（即從CEO到每個員工的路徑），也可以使用遞歸CTE。

• 建立帶有

  manager_id

  的測試表

mysql> create table employees.employees_mgr ( 
    -> id int primary key not null,
    -> name varchar(100) not null,
    -> manager_id int null,
    -> index (manager_id),
    -> foreign key (manager_id) references employees_mgr (id)
    -> );
Query OK, 0 rows affected (0.03 sec)
           

• 插入示例資料

mysql> insert into employees.employees_mgr values 
    -> (333, "Yasmina", null), /* Yasmina is the CEO (manager_id is null) */
    -> (198, "John", 333), /* John has id 198 and reports to 333 (Yasmina) */
    -> (692, "Tarek", 333),
    -> (29, "Pedro ", 198),
    -> (4610, "Sarah", 29),
    -> (72, "Pierre", 29),
    -> (123, "Adil", 692);
Query OK, 7 rows affected (0.01 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> select id, name, manager_id from employees.employees_mgr;
+------+---------+------------+
| id   | name    | manager_id |
+------+---------+------------+
|   29 | Pedro   |        198 |
|   72 | Pierre  |         29 |
|  123 | Adil    |        692 |
|  198 | John    |        333 |
|  333 | Yasmina |       NULL |
|  692 | Tarek   |        333 |
| 4610 | Sarah   |         29 |
+------+---------+------------+
7 rows in set (0.00 sec)
           

• 執行遞歸CTE

mysql> with recursive employee_paths (id, name, path) as (
    -> select id, name, cast(id as char(200))
    -> from employees.employees_mgr
    -> where manager_id is null
    -> union all
    -> select e.id, e.name, concat(ep.path, '->', e.id)
    -> from employee_paths as ep
    -> join employees.employees_mgr as e
    -> on ep.id = e.manager_id
    -> )
    -> select * from employee_paths;
+------+---------+--------------------+
| id   | name    | path               |
+------+---------+--------------------+
|  333 | Yasmina | 333                |
|  198 | John    | 333->198           |
|  692 | Tarek   | 333->692           |
|   29 | Pedro   | 333->198->29       |
|  123 | Adil    | 333->692->123      |
|   72 | Pierre  | 333->198->29->72   |
| 4610 | Sarah   | 333->198->29->4610 |
+------+---------+--------------------+
7 rows in set (0.00 sec)
           

  

with recursive employee_paths (id, name, path) as

是CTE的名稱，列是

(id, name, path)

。

  

select id, name, cast(id as char(200)) from employees.employees_mgr where manager_id is null

是查詢CEO的

seed查詢

（ 沒有在CEO之上的管理者）。

  

select e.id, e.name, concat(ep.path, '->', e.id) from employee_paths as ep join employees.employees_mgr as e on ep.id = e.manager_id

是遞歸查詢。

  遞歸查詢生成的每一行，會查找直接向前一行生成的員工做彙報的所有員工。對于每個員工，該行的資訊包括員工ID、 姓名和員工管理鍊，該鍊是在最後添加了員工ID的管理鍊。