SQL 查詢之公用表表達式（CTE）
CTE 的全稱是 common table expression，即公用表表達式，它可以簡化 SQL 查詢代碼。CTE 是可命名的子查詢，它是在 SQL 查詢執(zhí)行期間臨時創(chuàng)建的，包含列與記錄的虛擬表。CTE 只能被調(diào)用它的主查詢訪問，主查詢執(zhí)行完畢后會被刪除。
CTE 可以簡化查詢語句
CTE的基礎(chǔ)語法如下。
1WITH my_cte AS (
2 SELECT a,b,c
3 FROM T1
4)
5SELECT a,c
6FROM my_cte
7WHERE ....CTE 語句以 WITH 關(guān)鍵字開始，所以 CTE 又被稱為 WITH 語句。在 WITH 關(guān)鍵字之后是 CTE 的名字；在 AS 關(guān)鍵字之后的括號里寫入要實現(xiàn)的查詢語句。
在上例中，CTE 的名字是 my_cte，查詢語句為 SELECT a,b,c FROM T1。
主查詢在定義 CTE 語句的括號后面。定義 CTE 之后，即可在主查詢中通過 CTE 的名字對其進行引用。主查詢也稱為外部查詢，在這個示例中是 SELECT a,c FROM my_cte WHERE ...。
簡單的 CTE 示例
本文中的例子都基于以下這個 sales 表，這個表模擬了一家連鎖書店的銷售數(shù)據(jù)，示例如下。
示例數(shù)據(jù)
生成 sales 表的 SQL 代碼如下。
1-- 創(chuàng)建名為sales的表
2CREATE TABLE sales (
3 branch VARCHAR(50),
4 date DATE,
5 seller VARCHAR(50),
6 item VARCHAR(50),
7 quantity INT,
8 unit_price DECIMAL(10, 2)
9);
10
11-- 插入數(shù)據(jù)
12INSERT INTO sales (branch, date, seller, item, quantity, unit_price)
13VALUES
14 ('Beijing-1', '2024-06-07', 'Zhangsan', 'SQL Data Analysis', 1, 80),
15 ('Shanghai-1', '2024-06-06', 'Lisi', 'Python Data Analysis', 2, 120),
16 ('Shanghai-2', '2024-06-07', 'Wangwu', 'Pandas Workshop', 1, 60),
17 ('Beijing-1', '2024-06-07', 'Zhangsan', 'Polars in Action', 1, 50),
18 ('Shanghai-2', '2024-06-07', 'Wangwu', 'Echarts Data Charts', 2, 90),
19 ('Shanghai-1', '2024-06-07', 'Lisi', 'Mastering Pandas', 5, 75),
20 ('Shanghai-1', '2024-06-07', 'Zhaoliu', 'R Projects', 2, 100);我們先看一個簡單的例子。輸出 sales 表中的原始數(shù)據(jù)記錄，但要在 sales 表中再添加一列，顯示當(dāng)天（date）在同一分店中售出圖書的最高價格。獲取售價最高的圖書價格可以使用如下 CTE。
1WITH highest AS (
2 SELECT
3 branch,
4 date,
5 MAX(unit_price) AS highest_price
6 FROM sales
7 GROUP BY branch, date
8)
9SELECT
10 sales.*,
11 h.highest_price
12FROM sales
13JOIN highest h
14 ON sales.branch = h.branch
15 AND sales.date = h.date上述查詢定義了名為 highest 的 CTE，這個虛擬表包含的列為 branch、date 和highest_price，分別表示分店的名稱、銷售日期和當(dāng)天該分店銷售的最高單價。
然后，在主查詢中，像使用常規(guī)表一樣使用虛擬表 highest。
最后，將 highest 的查詢結(jié)果與 sales 表進行連接。
查詢的結(jié)果如下。
查詢結(jié)果
下面這個例子顯示各分店最高的單日收入。
1WITH daily_revenue AS (
2 SELECT
3 branch,
4 date,
5 SUM(unit_price * quantity) AS daily_revenue
6 FROM sales
7 WHERE EXTRACT(YEAR FROM date) = 2024
8 GROUP BY 1,2
9)
10SELECT
11 branch,
12 MAX(daily_revenue) max_daily_revenue
13FROM daily_revenue
14GROUP BY 1
15ORDER BY 2 DESC在這段代碼中，首先定義名為 daily_revenue 的 CTE，包含的列為 branch、date 和 daily_revenue，數(shù)據(jù)記錄為每家分店在 2024 年內(nèi)每一天的日銷售額。
然后，在主查詢中，獲取 2024 年每家分店的最高日銷售額。
最后，按max_daily_revenue 降序排列輸出數(shù)據(jù)。
查詢結(jié)果
在 SQL 高級查詢中使用 CTE
主查詢中可以使用多個 CTE。下面的示例中介紹如何使用多個 CTE 對復(fù)雜的 SQL 語句進行簡化。
假設(shè)要顯示某個城市的月銷售額，以及該城市中每個分店的銷售額。
在本例中，先創(chuàng)建兩個 CTE。再在主查詢中把這兩個 CTE 連接起來。
1WITH shanghai1_monthly_revenue AS (
2 SELECT
3 EXTRACT(MONTH FROM date) as month,
4 SUM(unit_price * quantity) AS revenue
5 FROM sales
6 WHERE EXTRACT(YEAR FROM date) = 2024
7 AND branch = 'Shanghai-1'
8 GROUP BY 1
9),
10shanghai2_monthly_revenue AS (
11 SELECT
12 EXTRACT(MONTH FROM date) as month,
13 SUM(unit_price * quantity) AS revenue
14 FROM sales
15 WHERE EXTRACT(YEAR FROM date) = 2024
16 AND branch = 'Shanghai-2'
17 GROUP BY 1
18)
19SELECT
20 s1.month,
21 s1.revenue + s2.revenue AS shanghai_revenue,
22 s1.revenue AS shanghai1_revenue,
23 s2.revenue AS shanghai2_revenue
24FROM shanghai1_monthly_revenue s1, shanghai2_monthly_revenue s2
25WHERE s1.month = s2.month在上述代碼中，定義了 shanghai1_monthly_revenue 與 shanghai2_monthly_revenue 兩個 CTE，使用這兩個 CTE 獲取 2024 年上海各分店的月銷售額。
接下來，使用 month 列連接這兩個 CTE，并把兩個分店的銷售額相加，以獲取上海的總銷售額。
查詢結(jié)果如下。
查詢結(jié)果
下例顯示每家分店最大金額的訂單及該訂單的日期。為此，需要創(chuàng)建一個 CTE，根據(jù)訂單金額為各分店的訂單進行排名（position 列就是排名）。
1WITH tickets AS (
2 SELECT distinct
3 branch,
4 date,
5 unit_price * quantity AS ticket_amount,
6 ROW_NUMBER() OVER (
7 PARTITION BY branch
8 ORDER by unit_price * quantity DESC
9 ) AS position
10 FROM sales
11 ORDER BY 3 DESC
12)
13SELECT
14 branch,
15 date,
16 ticket_amount
17FROM tickets
18WHERE position =1在這段代碼中，創(chuàng)建了名為 tickets 的 CTE，包含 branch、date、titcket_amount和 position。
在主查詢中，過濾 position 等于 1 的記錄，以獲取各分店銷售額最大的訂單。
查詢結(jié)果如下。
查詢結(jié)果
SQL 查詢中的嵌套 CTE
本例介紹如何使用嵌套 CTE。輸出所有單價超過 90 元的圖書，以及上海分店（Shanghai-2）的銷售數(shù)量。
1WITH over_90_items AS (
2 SELECT DISTINCT
3 item,
4 unit_price
5 FROM sales
6 WHERE unit_price >=90
7),
8shanghai2_over_90 AS (
9 SELECT
10 o90.item,
11 o90.unit_price,
12 coalesce(SUM(s.quantity), 0) as total_sold
13 FROM over_90_items o90
14 LEFT JOIN sales s
15 ON o90.item = s.item AND s.branch = 'Shanghai-2'
16 GROUP BY o90.item, o90.unit_price
17)
18SELECT item, unit_price, total_sold
19FROM shanghai2_over_90;over_90_items 用于篩選單價大于或等于 90 元的圖書。
shanghai2_over_90 用于篩選 Shanghai-2 分店售出的超過 90 元的圖書的數(shù)量。
這就是嵌套 CTE 的基本用法。
注意，shanghai2_over_90 中的 FROM 子句中引用了 over_90_items。
使用 LEFT JOIN sales 的原因是 Shanghai-2 分店可能沒有銷售過價格超過 90 元的圖書。
查詢結(jié)果如下。
查詢結(jié)果
遞歸查詢與 CTE
在關(guān)系型數(shù)據(jù)庫中，表示數(shù)據(jù)層級結(jié)構(gòu)的表很常見，例如，上下級關(guān)系、父子組件關(guān)系等。
為了在這些層級結(jié)構(gòu)中進行從上到下或從下到上的遍歷，可以使用遞歸 CTE。
RECURSIVE 是定義遍歷遞歸數(shù)據(jù)結(jié)構(gòu)的 CTE 的關(guān)鍵字。CTE 遞歸查詢的形式如下。
1WITH RECURSIVE cte_name AS (
2 CTE_query_definition -- 非遞歸查詢項
3UNION ALL
4 CTE_query_definition -- 遞歸查詢項
5)
6SELECT * FROM cte_name;限于本文的篇幅，有 CTE 的遞歸查詢就先不贅述了，關(guān)注我們的公眾號，我會在后續(xù)的文章中進行介紹。
結(jié)語
SQL 的 CTE 非常強大，它可以讓 SQL 查詢代碼更簡潔，邏輯更清晰。
