前言

公用表表達(dá)式（Common Table Expression，CTE）和派生表類(lèi)似，都是虛擬的表，但是相比于派生表，CTE具有一些優(yōu)勢(shì)和方便之處。

CTE有兩種類(lèi)型：非遞歸的CTE和遞歸CTE。

CTE是標(biāo)準(zhǔn)SQL的特性，屬于表表達(dá)式的一種，MariaDB支持CTE，MySQL 8才開(kāi)始支持CTE。

1.非遞歸CTE

CTE是使用WITH子句定義的，包括三個(gè)部分：CTE名稱(chēng)cte_name、定義CTE的查詢(xún)語(yǔ)句inner_query_definition和引用CTE的外部查詢(xún)語(yǔ)句outer_query_definition。

它的格式如下：

WITH cte_name1[(column_name_list)] AS (inner_query_definition_1)
   [,cte_name2[(column_name_list)] AS (inner_query_definition_2)]
[,...]
outer_query_definition

其中column_name_list指定inner_query_definition中的列列表名，如果不寫(xiě)該選項(xiàng)，則需要保證在inner_query_definition中的列都有名稱(chēng)且唯一，即對(duì)列名有兩種命名方式：內(nèi)部命名和外部命名。

注意，outer_quer_definition必須和CTE定義語(yǔ)句同時(shí)執(zhí)行，因?yàn)镃TE是臨時(shí)虛擬表，只有立即引用它，它的定義才是有意義的。

下面語(yǔ)句是一個(gè)簡(jiǎn)單的CTE的用法。首先定義一張?zhí)摂M表，也就是CTE，然后在外部查詢(xún)中引用它。

CREATE OR REPLACE TABLE t(id INT NOT NULL PRIMARY KEY,sex CHAR(3),NAME CHAR(20));
INSERT INTO t VALUES (1,'nan','David'),(2,'nv','Mariah'),(3,'nv','gaoxiaofang'),(4,'nan','Jim'),
        (5,'nv','Selina'),(6,'nan','John'),(7,'nan','Monty'),(8,'nv','xiaofang');
 
# 定義CTE，順便為每列重新命名，且使用ORDER BY子句
WITH nv_t(myid,mysex,myname) AS (
    SELECT * FROM t WHERE sex='nv' ORDER BY id DESC
)
# 使用CTE
SELECT * FROM nv_t;
+------+-------+-------------+
| myid | mysex | myname      |
+------+-------+-------------+
|    2 | nv    | Mariah      |
|    3 | nv    | gaoxiaofang |
|    5 | nv    | Selina      |
|    8 | nv    | xiaofang    |
+------+-------+-------------+

從結(jié)果中可以看到，在CTE的定義語(yǔ)句中使用ORDER BY子句是沒(méi)有任何作用的。

在這里可以發(fā)現(xiàn)，CTE和派生表需要滿(mǎn)足的幾個(gè)共同點(diǎn)：每一列要求有列名，包括計(jì)算列；列名必須唯一；不能使用ORDER BY子句，除非使用了TOP關(guān)鍵字(標(biāo)準(zhǔn)SQL嚴(yán)格遵守不能使用ORDER BY的規(guī)則，但MySQL/MariaDB中允許)。不僅僅是CTE和派生表，其他表表達(dá)式(內(nèi)聯(lián)表值函數(shù)(sql server才支持)、視圖)也都要滿(mǎn)足這些條件。究其原因，表表達(dá)式的本質(zhì)是表，盡管它們是虛擬表，也應(yīng)該滿(mǎn)足形成表的條件。

一方面，在關(guān)系模型中，表對(duì)應(yīng)的是關(guān)系，表中的行對(duì)應(yīng)的是關(guān)系模型中的元組，表中的字段（或列）對(duì)應(yīng)的是關(guān)系中的屬性。屬性由三部分組成：屬性的名稱(chēng)、屬性的類(lèi)型和屬性值。因此要形成表，必須要保證屬性的名稱(chēng)，即每一列都有名稱(chēng)，且唯一。

另一方面，關(guān)系模型是基于集合的，在集合中是不要求有序的，因此不能在形成表的時(shí)候讓數(shù)據(jù)按序排列，即不能使用ORDER BY子句。之所以在使用了TOP后可以使用ORDER BY子句，是因?yàn)檫@個(gè)時(shí)候的ORDER BY只為T(mén)OP提供數(shù)據(jù)的邏輯提取服務(wù)，并不提供排序服務(wù)。例如使用ORDER BY幫助TOP選擇出前10行，但是這10行數(shù)據(jù)在形成表的時(shí)候不保證是順序的。

相比派生表，CTE有幾個(gè)優(yōu)點(diǎn)：

1.多次引用：避免重復(fù)書(shū)寫(xiě)。

2.多次定義：避免派生表的嵌套問(wèn)題。

3.可以使用遞歸CTE，實(shí)現(xiàn)遞歸查詢(xún)。

例如：

# 多次引用，避免重復(fù)書(shū)寫(xiě)
WITH nv_t(myid,mysex,myname) AS (
    SELECT * FROM t WHERE sex='nv'
)
SELECT t1.*,t2.*
FROM nv_t t1 JOIN nv_t t2
WHERE t1.myid = t2.myid+1;
 
# 多次定義，避免派生表嵌套
WITH
nv_t1 AS (          /* 第一個(gè)CTE */
    SELECT * FROM t WHERE sex='nv' 
),
nv_t2 AS (          /* 第二個(gè)CTE */
    SELECT * FROM nv_t1 WHERE id>3
)
SELECT * FROM nv_t2;

如果上面的語(yǔ)句不使用CTE而使用派生表的方式，則它等價(jià)于：

SELECT * FROM
(SELECT * FROM
(SELECT * FROM t WHERE sex='nv') AS nv_t1) AS nv_t2;

2.遞歸CTE

SQL語(yǔ)言是結(jié)構(gòu)化查詢(xún)語(yǔ)言，它的遞歸特性非常差。使用遞歸CTE可稍微改善這一缺陷。

公用表表達(dá)式(CTE)具有一個(gè)重要的優(yōu)點(diǎn)，那就是能夠引用其自身，從而創(chuàng)建遞歸CTE。遞歸CTE是一個(gè)重復(fù)執(zhí)行初始CTE以返回?cái)?shù)據(jù)子集直到獲取完整結(jié)果集的公用表表達(dá)式。

當(dāng)某個(gè)查詢(xún)引用遞歸CTE時(shí)，它即被稱(chēng)為遞歸查詢(xún)。遞歸查詢(xún)通常用于返回分層數(shù)據(jù)，例如：顯示某個(gè)組織圖中的雇員或物料清單方案(其中父級(jí)產(chǎn)品有一個(gè)或多個(gè)組件，而那些組件可能還有子組件，或者是其他父級(jí)產(chǎn)品的組件)中的數(shù)據(jù)。

遞歸CTE可以極大地簡(jiǎn)化在SELECT、INSERT、UPDATE、DELETE或CREATE VIEW語(yǔ)句中運(yùn)行遞歸查詢(xún)所需的代碼。

也就是說(shuō)，遞歸CTE通過(guò)引用自身來(lái)實(shí)現(xiàn)。它會(huì)不斷地重復(fù)查詢(xún)每一次遞歸得到的子集，直到得到最后的結(jié)果。這使得它非常適合處理"樹(shù)狀結(jié)構(gòu)"的數(shù)據(jù)或者有"層次關(guān)系"的數(shù)據(jù)。

2.1 語(yǔ)法

遞歸cte中包含一個(gè)或多個(gè)定位點(diǎn)成員，一個(gè)或多個(gè)遞歸成員，最后一個(gè)定位點(diǎn)成員必須使用"union [all]"(mariadb中的遞歸CTE只支持union [all]集合算法)聯(lián)合第一個(gè)遞歸成員。

以下是單個(gè)定位點(diǎn)成員、單個(gè)遞歸成員的遞歸CTE語(yǔ)法：

with recursive cte_name as (
    select_statement_1       /* 該cte_body稱(chēng)為定位點(diǎn)成員 */
  union [all]
    cte_usage_statement      /* 此處引用cte自身，稱(chēng)為遞歸成員 */
)
outer_definition_statement    /* 對(duì)遞歸CTE的查詢(xún)，稱(chēng)為遞歸查詢(xún) */

其中：

select_statement_1：稱(chēng)為"定位點(diǎn)成員"，這是遞歸cte中最先執(zhí)行的部分，也是遞歸成員開(kāi)始遞歸時(shí)的數(shù)據(jù)來(lái)源。

cte_usage_statement：稱(chēng)為"遞歸成員"，該語(yǔ)句中必須引用cte自身。它是遞歸cte中真正開(kāi)始遞歸的地方，它首先從定位點(diǎn)成員處獲取遞歸數(shù)據(jù)來(lái)源，然后和其他數(shù)據(jù)集結(jié)合開(kāi)始遞歸，每遞歸一次都將遞歸結(jié)果傳遞給下一個(gè)遞歸動(dòng)作，不斷重復(fù)地查詢(xún)后，當(dāng)最終查不出數(shù)據(jù)時(shí)才結(jié)束遞歸。

outer_definition_statement：是對(duì)遞歸cte的查詢(xún)，這個(gè)查詢(xún)稱(chēng)為"遞歸查詢(xún)"。

2.2 遞歸CTE示例(1)

舉個(gè)最經(jīng)典的例子：族譜。

例如，下面是一張族譜表

CREATE OR REPLACE TABLE fork(id INT NOT NULL UNIQUE,NAME CHAR(20),father INT,mother INT);
INSERT INTO fork VALUES
    (1,'chenyi',2,3),(2,'huagner',4,5),(3,'zhangsan',NULL,NULL),
    (4,'lisi',6,7),(5,'wangwu',8,9),(6,'zhaoliu',NULL,NULL),(7,'sunqi',NULL,NULL),
    (8,'songba',NULL,NULL),(9,'yangjiu',NULL,NULL);
 
MariaDB [test]> select * from fork;
+----+----------+--------+--------+
| id | name     | father | mother |
+----+----------+--------+--------+
|  1 | chenyi   |      2 |      3 |
|  2 | huagner  |      4 |      5 |
|  3 | zhangsan |   NULL |   NULL |
|  4 | lisi     |      6 |      7 |
|  5 | wangwu   |      8 |      9 |
|  6 | zhaoliu  |   NULL |   NULL |
|  7 | sunqi    |   NULL |   NULL |
|  8 | songba   |   NULL |   NULL |
|  9 | yangjiu  |   NULL |   NULL |
+----+----------+--------+--------+

該族譜表對(duì)應(yīng)的結(jié)構(gòu)圖：

如果要找族譜中某人的父系，首先在定位點(diǎn)成員中獲取要從誰(shuí)開(kāi)始找，例如上圖中從"陳一"開(kāi)始找。那么陳一這個(gè)記錄就是第一個(gè)遞歸成員的數(shù)據(jù)源，將這個(gè)數(shù)據(jù)源聯(lián)接族譜表，找到陳一的父親黃二，該結(jié)果將通過(guò)union子句結(jié)合到上一個(gè)"陳一"中。再次對(duì)黃二遞歸，找到李四，再對(duì)李四遞歸找到趙六，對(duì)趙六遞歸后找不到下一個(gè)數(shù)據(jù)，所以這一分支的遞歸結(jié)束。

遞歸cte的語(yǔ)句如下：

WITH recursive fuxi AS (
    SELECT * FROM fork WHERE `name`='chenyi'
    UNION
    SELECT f.* FROM fork f JOIN fuxi a WHERE f.id=a.father
)
SELECT * FROM fuxi;

演變結(jié)果如下：

首先執(zhí)行定位點(diǎn)部分的語(yǔ)句，得到定位點(diǎn)成員，即結(jié)果中的第一行結(jié)果集：

根據(jù)該定位點(diǎn)成員，開(kāi)始執(zhí)行遞歸語(yǔ)句：

遞歸時(shí)，按照f(shuō).id=a.father的條件進(jìn)行篩選，得到id=2的結(jié)果，該結(jié)果通過(guò)union和之前的數(shù)據(jù)結(jié)合起來(lái)，作為下一次遞歸的數(shù)據(jù)源fuxi。

再進(jìn)行第二次遞歸：

第三次遞歸：

由于第三次遞歸后，id=6的father值為null，因此第四次遞歸的結(jié)果為空，于是遞歸在第四次之后結(jié)束。

2.2 遞歸CTE示例(2)

該CTE示例主要目的是演示切換遞歸時(shí)的字段名稱(chēng)。

例如，有幾個(gè)公交站點(diǎn)，它們之間的互通性如下圖：

對(duì)應(yīng)的表為：

CREATE OR REPLACE TABLE bus_routes (src char(50), dst char(50));
INSERT INTO bus_routes VALUES 
  ('stopA','stopB'),('stopB','stopA'),('stopA','stopC'),('stopC','stopB'),('stopC','stopD');
MariaDB [test]> select * from bus_routes;
+-------+-------+
| src   | dst   |
+-------+-------+
| stopA | stopB |
| stopB | stopA |
| stopA | stopC |
| stopC | stopB |
| stopC | stopD |
+-------+-------+

要計(jì)算以stopA作為起點(diǎn)，能到達(dá)哪些站點(diǎn)的遞歸CTE如下：

WITH recursive dst_stop AS (
    SELECT src AS dst FROM bus_routes WHERE src='stopA'   /* note: src as dst */
    UNION
    SELECT b.dst FROM bus_routes b 
      JOIN dst_stop d 
    WHERE d.dst=b.src
)
SELECT * FROM dst_stop;

結(jié)果如下：

+-------+
| dst   |
+-------+
| stopA |
| stopB |
| stopC |
| stopD |
+-------+

首先執(zhí)行定位點(diǎn)語(yǔ)句，得到定位點(diǎn)成員stopA，字段名為dst。

再將定位點(diǎn)成員結(jié)果和bus_routes表聯(lián)接進(jìn)行第一次遞歸，如下圖：

再進(jìn)行第二次遞歸：

再進(jìn)行第三次遞歸，但第三次遞歸過(guò)程中，stopD找不到對(duì)應(yīng)的記錄，因此遞歸結(jié)束。

2.2 遞歸CTE示例(3)

仍然是公交路線圖：

計(jì)算以stopA為起點(diǎn)，可以到達(dá)哪些站點(diǎn)，并給出路線圖。例如:stopA-->stopC-->stopD。

以下是遞歸CTE語(yǔ)句：

WITH recursive bus_path(bus_path,bus_dst) AS (
    SELECT src,src FROM bus_routes WHERE src='stopA'
    UNION
    SELECT CONCAT(b2.bus_path,'-->',b1.dst),b1.dst
    FROM bus_routes b1
      JOIN bus_path b2
    WHERE b2.bus_dst = b1.src AND LOCATE(b1.dst,b2.bus_path)=0
)
SELECT * FROM bus_path;

首先獲取起點(diǎn)stopA，再獲取它的目標(biāo)stopB和stopC，并將起點(diǎn)到目標(biāo)使用"-->"連接，即concat(src,"-->","dst")。再根據(jù)stopB和stopC，獲取它們的目標(biāo)。stopC的目標(biāo)為stopD和stopB，stopB的目標(biāo)為stopA。如果連接成功，那么路線為：

stopA-->stopB-->stopA   目標(biāo)：stopA
stopA-->stopC-->stopD   目標(biāo)：stopD
stopA-->stopC-->stopB   目標(biāo)：stopB

這樣會(huì)無(wú)限遞歸下去，因此我們要判斷何時(shí)結(jié)束遞歸。判斷的方法是目標(biāo)不允許出現(xiàn)在路線中，只要出現(xiàn)，說(shuō)明路線會(huì)重復(fù)計(jì)算。

總結(jié)