對(duì)于很多 IT 從業(yè)人員來說，SQL 僅僅意味著簡單的增刪改查（SELECT、INSERT、UPDATE 和 DELETE），但實(shí)際上 SQL 能夠?qū)崿F(xiàn)的功能遠(yuǎn)遠(yuǎn)不止簡單的增刪改查。

今天我們就來介紹一個(gè)高級(jí) SQL 功能：通用表表達(dá)式（Common Table Expression）。CTE 可以提高復(fù)雜查詢的性能和可讀性，實(shí)現(xiàn)樹狀結(jié)構(gòu)或者圖數(shù)據(jù)的遍歷，實(shí)現(xiàn)以下功能：

• 生成數(shù)字序列；
• 獲取員工上下級(jí)的組織關(guān)系；
• 查詢地鐵、航班換乘線路；
• 社交網(wǎng)絡(luò)圖譜分析。

一般來說，我們只能通過應(yīng)用程序或者存儲(chǔ)過程實(shí)現(xiàn)這些復(fù)雜的功能。但是有了 CTE，我們可以直接利用一個(gè) SQL 語句完成以上功能。CTE 不僅強(qiáng)大而且通用，各種主流數(shù)據(jù)庫都提供了支持。

我們通過幾個(gè)實(shí)用案例，了解一下 CTE 的語法，同時(shí)介紹各種數(shù)據(jù)庫中的實(shí)現(xiàn)差異。

簡單 CTE

通用表表達(dá)式使用 WITH 關(guān)鍵字表示，例如：

WITH t(n) AS (
  SELECT 4
)
SELECT * FROM t;
n|
-|
4|

以上 WITH 子句相當(dāng)于定義了一個(gè)語句級(jí)別的臨時(shí)表 t(n)，在隨后的 SELECT、INSERT、UPDATE 以及 DELETE 語句中都可以使用。

WITH 子句定義了一個(gè)表達(dá)式，表達(dá)式的值是一個(gè)表，所以稱為通用表表達(dá)式。CTE 和子查詢類似，可以用于 SELECT、INSERT、UPDATE 以及 DELETE 語句。Oracle 中稱之為子查詢因子（subquery factoring）

CTE 與子查詢類似，只在當(dāng)前語句中有效；不過一個(gè)語句中可以定義多個(gè) CTE，而且 CTE 被定義之后可以多次引用：

WITH t1(n) AS (
  SELECT 4 -- FROM dual
),
t2(n) AS (
  SELECT n+1 FROM t1
)
SELECT t1.n, t2.n
  FROM t1 
 CROSS JOIN t2;
n|n|
-|-|
4|5|

第一個(gè) CTE 名為 t1；第二個(gè) CTE 名為 t2，引用了前面定義的 t1 ；每個(gè) CTE 之間使用逗號(hào)進(jìn)行分隔；最后的 SELECT 語句使用前面定義的 2 個(gè) CTE 進(jìn)行連接查詢。這種使用 CTE 的方法和編程語言中的變量非常類似。

CTE 和視圖、臨時(shí)表或者子查詢都有點(diǎn)類似，但是比它們的結(jié)構(gòu)更加清晰；數(shù)據(jù)庫對(duì)于 CTE 只需要執(zhí)行一次，性能也會(huì)更好。不過，CTE 真正強(qiáng)大之處是允許在定義中調(diào)用自己，也就是遞歸調(diào)用。

生成數(shù)字序列

WITH 子句還有一種遞歸形式，以下語句可以生成一個(gè) 1 到 10 的數(shù)字序列：

WITH RECURSIVE t(n) AS
(
  SELECT 1 -- 初始化
   UNION ALL
  SELECT n + 1 FROM t WHERE n < 10 -- 遞歸結(jié)束條件
)
SELECT n FROM t;
n |
--|
 1|
 2|
 3|
 4|
 5|
 6|
 7|
 8|
 9|
10|

其中，RECURSIVE 表示遞歸查詢，Oracle 和 SQL Server 中不需要該關(guān)鍵字。

遞歸 CTE 包含兩部分，UNION ALL 上面的查詢語句用于生成初始化數(shù)據(jù)；下面的查詢語句用于遞歸，引用了它自身（ t ）。

• 運(yùn)行初始化語句，生成數(shù)字 1；
• 第 1 次運(yùn)行遞歸部分，此時(shí) n 等于 1，返回?cái)?shù)字 2（ n+1 ）；
• 第 2 次運(yùn)行遞歸部分，此時(shí) n 等于 2，返回?cái)?shù)字 3（ n+1 ）；
• 第 9 次運(yùn)行遞歸部分，此時(shí) n 等于 9，返回?cái)?shù)字 10（ n+1 ）；
• 第 10 次運(yùn)行遞歸部分，此時(shí) n 等于 10；由于查詢不滿足條件（ WHERE n < 10 ），不返回任何結(jié)果，并且遞歸結(jié)束；最后的查詢語句返回 t 中的全部數(shù)據(jù)，也就是一個(gè) 1 到 10 的數(shù)字序列。

只要是具有一定規(guī)律的數(shù)字序列都可以通過遞歸 CTE 生成，例如斐波那契數(shù)列。

遍歷組織結(jié)構(gòu)圖

在公司的組織結(jié)構(gòu)中，存在上下級(jí)的管理關(guān)系，如下圖所示。

示例表和數(shù)據(jù)：https://github.com/dongxuyang1985/thinking_in_sql

如果我們想要知道某個(gè)員工從上至下的各級(jí)領(lǐng)導(dǎo)，可以使用遞歸 CTE：

WITH RECURSIVE employee_path (emp_id, emp_name, path) AS
(
  SELECT emp_id, emp_name, CAST(emp_name AS CHAR(100)) AS path
    FROM employee
   WHERE manager IS NULL
   UNION ALL
  SELECT e.emp_id, e.emp_name, CAST(CONCAT(ep.path, '->', e.emp_name) AS CHAR(1000))
    FROM employee_path ep
    JOIN employee e ON ep.emp_id = e.manager
)
SELECT * FROM employee_path WHERE emp_name = '黃忠';
emp_id|emp_name|path             |
------|--------|-----------------|
     5|黃忠    |劉備->諸葛亮->黃忠|

上面是 MySQL 中的語法。

Oracle 以及 SQL Server 中需要將 CHAR(100) 改為 VARCHAR(100)，同時(shí)省略 RECURSIVE 關(guān)鍵字；PostgreSQL 中需要將 CAST 函數(shù)里的 CHAR(100) 改為 VARCHAR(100)；SQLite 沒有提供 CONCAT 函數(shù)，使用連接操作符（||）即可。

其中，初始化查詢用于查找沒有 manager 的員工，也就是最上級(jí)的領(lǐng)導(dǎo)；遞歸查詢通過將員工的 manager 和上級(jí)員工的 emp_id 進(jìn)行關(guān)聯(lián)，獲取上下級(jí)管理關(guān)系；遞歸結(jié)束的條件就是沒有找到任何數(shù)據(jù)。當(dāng)然，我們也可以從下級(jí)往上級(jí)進(jìn)行遍歷。

其他具有這種層級(jí)關(guān)系的數(shù)據(jù)包括多層菜單、博客文章中的評(píng)論等。

查找地鐵換乘線路

地鐵、公交、航班等，包括社交網(wǎng)站上的關(guān)注，都是一種有向圖數(shù)據(jù)結(jié)構(gòu)。我們通常需要查找某一站點(diǎn)到另一站點(diǎn)的最短路徑，利用遞歸 CTE 可以實(shí)現(xiàn)這類需求。

示例表和數(shù)據(jù)：https://github.com/dongxuyang1985/sql_in_action

以下語句用于查找“王府井”到“積水潭”的換乘路線，使用 PostgreSQL 數(shù)據(jù)庫實(shí)現(xiàn)：

WITH RECURSIVE paths (start_station, stop_station, stops, path) AS (
  SELECT station_name, next_station, 1, ARRAY[station_name::text, next_station::text]
    FROM bj_subway WHERE station_name = '王府井'
   UNION ALL
  SELECT p.start_station, e.next_station, stops + 1, p.path || ARRAY[e.next_station::text]
    FROM paths p
    JOIN bj_subway e
      ON p.stop_station = e.station_name AND NOT e.next_station = ANY(p.path)
)
SELECT * FROM paths WHERE stop_station = '積水潭';
start_station|stop_station|stops|path                                                                           |
-------------|------------|-----|-------------------------------------------------------------------------------|
王府井        |積水潭      |    8|{王府井,天安門東,天安門西,西單,復(fù)興門,阜成門,車公莊,西直門,積水潭}                                         |
王府井        |積水潭      |    9|{王府井,東單,建國門,朝陽門,東四十條,東直門,雍和宮,安定門,鼓樓大街,積水潭}                                     |
王府井        |積水潭      |   13|{王府井,東單,建國門,北京站,崇文門,前門,和平門,宣武門,長椿街,復(fù)興門,阜成門,車公莊,西直門,積水潭}                        |
王府井        |積水潭      |   18|{王府井,天安門東,天安門西,西單,復(fù)興門,長椿街,宣武門,和平門,前門,崇文門,北京站,建國門,朝陽門,東四十條,東直門,雍和宮,安定門,鼓樓大街,積水潭}|

查詢結(jié)果顯示有 4 條路線，如果選擇最短路線就是第一條。其中的 path 字段是個(gè)數(shù)組，用于存儲(chǔ)走過的站點(diǎn)；最后的 NOT e.next_station = ANY(p.path) 條件用于避免反復(fù)經(jīng)過同一個(gè)站點(diǎn)，因?yàn)榈罔F線路是一個(gè)雙向圖。

我們還可以進(jìn)一步計(jì)算換乘次數(shù)，實(shí)現(xiàn)最少換乘路線；如果在表中增加一些字段，記錄每兩個(gè)站點(diǎn)之間的時(shí)間和換乘時(shí)間，還可以計(jì)算最快路線。

其他數(shù)據(jù)庫沒有提供數(shù)組類型，但是可以使用其他方法實(shí)現(xiàn)，以下是 MySQL 中的實(shí)現(xiàn)：

WITH RECURSIVE paths (start_station, stop_station, stops, path) AS (
  SELECT station_name, next_station, 1, CAST(CONCAT(station_name , ',', next_station) AS CHAR(1000))
    FROM bj_subway WHERE station_name = '王府井'
   UNION ALL
  SELECT p.start_station, e.next_station, stops + 1, CONCAT_WS(',', p.path, e.next_station)
    FROM paths p
    JOIN bj_subway e
      ON p.stop_station = e.station_name AND (INSTR(p.path, e.next_station) = 0)
)
SELECT * FROM paths WHERE stop_station ='積水潭';

我們使用了逗號(hào)分隔符的字符串模擬數(shù)組的效果，這種方法也適用于其他數(shù)據(jù)庫。