MySQL8.0新特性：公用表表達(dá)式

公用表表達(dá)式（或通用表表達(dá)式）簡稱為CTE（Common Table Expressions）。CTE是一個(gè)命名的臨時(shí)結(jié)果集，作用范圍是當(dāng)前語句。CTE可以理解成一個(gè)可以復(fù)用的子查詢，當(dāng)然跟子查詢還是有點(diǎn)區(qū)別的，CTE可以引用其他CTE，但子查詢不能引用其他子查詢。所以，可以考慮代替子查詢。

依據(jù)語法結(jié)構(gòu)和執(zhí)行方式的不同，公用表表達(dá)式分為普通公用表表達(dá)式和遞歸公用表表達(dá)式2 種。

1. 普通公用表表達(dá)式

普通公用表表達(dá)式的語法結(jié)構(gòu)是：

WITH CTE名稱
AS （子查詢）
SELECT|DELETE|UPDATE 語句;

普通公用表表達(dá)式類似于子查詢，不過，跟子查詢不同的是，它可以被多次引用，而且可以被其他的普通公用表表達(dá)式所引用。

舉例：查詢員工所在的部門的詳細(xì)信息。

mysql> SELECT * FROM departments
 WHERE department_id IN (
 SELECT DISTINCT department_id
 FROM employees
 );
+---------------+------------------+------------+-------------+
| department_id | department_name | manager_id | location_id |
+---------------+------------------+------------+-------------+
| 10 | Administration | 200 | 1700 |
| 20 | Marketing | 201 | 1800 |
| 30 | Purchasing | 114 | 1700 |
| 40 | Human Resources | 203 | 2400 |
| 50 | Shipping | 121 | 1500 |
| 60 | IT | 103 | 1400 |
| 70 | Public Relations | 204 | 2700 |
| 80 | Sales | 145 | 2500 |
| 90 | Executive | 100 | 1700 |
| 100 | Finance | 108 | 1700 |
| 110 | Accounting | 205 | 1700 |
+---------------+------------------+------------+-------------+
11 rows in set (0.00 sec)

這個(gè)查詢也可以用普通公用表表達(dá)式的方式完成：

mysql> WITH emp_dept_id
 AS (SELECT DISTINCT department_id FROM employees)
 SELECT *
 FROM departments d JOIN emp_dept_id e
 ON d.department_id = e.department_id;
+---------------+------------------+------------+-------------+---------------+
| department_id | department_name | manager_id | location_id | department_id |
+---------------+------------------+------------+-------------+---------------+
| 90 | Executive | 100 | 1700 | 90 |
| 60 | IT | 103 | 1400 | 60 |
| 100 | Finance | 108 | 1700 | 100 |
| 30 | Purchasing | 114 | 1700 | 30 |
| 50 | Shipping | 121 | 1500 | 50 |
| 80 | Sales | 145 | 2500 | 80 |
| 10 | Administration | 200 | 1700 | 10 |
| 20 | Marketing | 201 | 1800 | 20 |
| 40 | Human Resources | 203 | 2400 | 40 |
| 70 | Public Relations | 204 | 2700 | 70 |
| 110 | Accounting | 205 | 1700 | 110 |
+---------------+------------------+------------+-------------+---------------+
11 rows in set (0.00 sec)

例子說明，公用表表達(dá)式可以起到子查詢的作用。以后如果遇到需要使用子查詢的場景，你可以在查詢之前，先定義公用表表達(dá)式，然后在查詢中用它來代替子查詢。而且，跟子查詢相比，公用表表達(dá)式有一個(gè)優(yōu)點(diǎn)，就是定義過公用表表達(dá)式之后的查詢，可以像一個(gè)表一樣多次引用公用表表達(dá)式，而子查詢則不能。

2. 遞歸公用表表達(dá)式

遞歸公用表表達(dá)式也是一種公用表表達(dá)式，只不過，除了普通公用表表達(dá)式的特點(diǎn)以外，它還有自己的特點(diǎn)，就是可以調(diào)用自己。它的語法結(jié)構(gòu)是：

WITH RECURSIVE
CTE名稱 AS （子查詢）
SELECT|DELETE|UPDATE 語句;

遞歸公用表表達(dá)式由 2 部分組成，分別是種子查詢和遞歸查詢，中間通過關(guān)鍵字 UNION [ALL]進(jìn)行連接。這里的種子查詢，意思就是獲得遞歸的初始值。這個(gè)查詢只會(huì)運(yùn)行一次，以創(chuàng)建初始數(shù)據(jù)集，之后遞歸查詢會(huì)一直執(zhí)行，直到?jīng)]有任何新的查詢數(shù)據(jù)產(chǎn)生，遞歸返回。

案例：針對(duì)于我們常用的employees表，包含employee_id，last_name和manager_id三個(gè)字段。如果a是b的管理者，那么，我們可以把b叫做a的下屬，如果同時(shí)b又是c的管理者，那么c就是b的下屬，是a的下下屬。

下面我們嘗試用查詢語句列出所有具有下下屬身份的人員信息。

如果用我們之前學(xué)過的知識(shí)來解決，會(huì)比較復(fù)雜，至少要進(jìn)行 4 次查詢才能搞定：

• 第一步，先找出初代管理者，就是不以任何別人為管理者的人，把結(jié)果存入臨時(shí)表；

• 第二步，找出所有以初代管理者為管理者的人，得到一個(gè)下屬集，把結(jié)果存入臨時(shí)表；

• 第三步，找出所有以下屬為管理者的人，得到一個(gè)下下屬集，把結(jié)果存入臨時(shí)表。

• 第四步，找出所有以下下屬為管理者的人，得到一個(gè)結(jié)果集。

如果第四步的結(jié)果集為空，則計(jì)算結(jié)束，第三步的結(jié)果集就是我們需要的下下屬集了，否則就必須繼續(xù)進(jìn)行第四步，一直到結(jié)果集為空為止。比如上面的這個(gè)數(shù)據(jù)表，就需要到第五步，才能得到空結(jié)果集。而且，最后還要進(jìn)行第六步：把第三步和第四步的結(jié)果集合并，這樣才能最終獲得我們需要的結(jié)果集。

如果用遞歸公用表表達(dá)式，就非常簡單了。我介紹下具體的思路。

• 用遞歸公用表表達(dá)式中的種子查詢，找出初代管理者。字段 n 表示代次，初始值為 1，表示是第一代管理者。

• 用遞歸公用表表達(dá)式中的遞歸查詢，查出以這個(gè)遞歸公用表表達(dá)式中的人為管理者的人，并且代次的值加 1。直到?jīng)]有人以這個(gè)遞歸公用表表達(dá)式中的人為管理者了，遞歸返回。

• 在最后的查詢中，選出所有代次大于等于 3 的人，他們肯定是第三代及以上代次的下屬了，也就是下下屬了。這樣就得到了我們需要的結(jié)果集。

這里看似也是 3 步，實(shí)際上是一個(gè)查詢的 3 個(gè)部分，只需要執(zhí)行一次就可以了。而且也不需要用臨時(shí)表保存中間結(jié)果，比剛剛的方法簡單多了。

代碼實(shí)現(xiàn)：

WITH RECURSIVE cte
AS
(
SELECT employee_id,last_name,manager_id,1 AS n FROM employees WHERE employee_id = 100
-- 種子查詢，找到第一代領(lǐng)導(dǎo)
UNION ALL
SELECT a.employee_id,a.last_name,a.manager_id,n+1 FROM employees AS a JOIN cte
ON (a.manager_id = cte.employee_id) -- 遞歸查詢，找出以遞歸公用表表達(dá)式的人為領(lǐng)導(dǎo)的人
)
SELECT employee_id,last_name FROM cte WHERE n >= 3;

總之，遞歸公用表表達(dá)式對(duì)于查詢一個(gè)有共同的根節(jié)點(diǎn)的樹形結(jié)構(gòu)數(shù)據(jù)，非常有用。它可以不受層級(jí)的限制，輕松查出所有節(jié)點(diǎn)的數(shù)據(jù)。如果用其他的查詢方式，就比較復(fù)雜了。

3. 小 結(jié)

公用表表達(dá)式的作用是可以替代子查詢，而且可以被多次引用。遞歸公用表表達(dá)式對(duì)查詢有一個(gè)共同根節(jié)點(diǎn)的樹形結(jié)構(gòu)數(shù)據(jù)非常高效，可以輕松搞定其他查詢方式難以處理的查詢。