認識 CTE: WITH

寫程式的時候，你可以把一段 code 拆出來取個名字——像是寫 function。CTE 也是這樣。你可以把 SELECT 子查詢從主查詢拆出來，給它一個名字，然後在主要 SQL 查詢裡用它。

CTE（Common Table Expressions，通用表達式）就像是寫 SQL 寫到快被巢狀查詢搞瘋時的一口新鮮空氣。它讓 SQL code 不只更好懂，還超級優雅。如果你以前常常被一堆巢狀查詢搞到眼花撩亂——現在就是時候來認識 CTE 的「魔法」啦。

想像你在蓋房子。通常大家都想趕快裝窗戶、裝門（也就是直接塞一堆巢狀查詢），就算牆還沒蓋好。但 CTE 不一樣：你會先畫個草圖——像是先做一個臨時表，規劃好房子的格局。然後一步一步把查詢的樓層蓋起來。超有型、超穩、超技術。

其實 CTE 就是你用 SELECT 查詢「現場」創造出來的虛擬表格。有點像子查詢，但更強大。 如果在寫程式時你會把一段邏輯拆成有意義的 function，SQL 裡這個角色就是 CTE。你寫一個 SELECT，給它一個名字——然後就能像用大查詢的一部分一樣用它。是不是很帥？當然啦。

SQL 子查詢範例：

-- 主查詢
SELECT *
FROM (
    SELECT *
    FROM 學生
    WHERE 分數 > 75
) AS 已篩選學生; -- 子查詢，取名為 已篩選學生

把子查詢拆出來：

-- CTE/子查詢，取名為 已篩選學生
WITH 已篩選學生 AS (
    SELECT *
    FROM 學生
    WHERE 分數 > 75
)

-- 主查詢
SELECT *
FROM 已篩選學生;

很神奇吧，其實子查詢比 CTE 早 20 年出現！SQL-89 標準就有子查詢，CTE 則是到 SQL-2009 才出現。

WITH 語法

CTE 以關鍵字 WITH 開頭，長這樣：

WITH cte_name AS (
    SELECT ... -- 你的查詢寫這裡
)

SELECT ...
FROM cte_name;

這裡：

• cte_name —— 你的 CTE 名字。可以自己取個有意義的名字，比如 high_scores、filtered_data 或 best_students。
• 括號 () 裡面寫的是查詢，用來準備後面要用的資料。
• 定義完 CTE 之後，你就能像用一般資料表一樣在主查詢裡用它。

範例 1：簡單 CTE

來看個實際例子。假設我們有一個 學生 表格——裡面有學生名單跟他們的分數：

我們的目標——選出分數大於 85 的學生，並顯示他們的資料。

不用 CTE 的寫法：

可以用子查詢做到：

SELECT *
FROM (
    SELECT *
    FROM 學生
    WHERE 分數 > 85
) AS 已篩選學生;

用 CTE 寫法——看起來超舒服：

WITH 已篩選學生 AS (
    SELECT *
    FROM 學生
    WHERE 分數 > 85
)
SELECT *
FROM 已篩選學生;

是不是看起來更乾淨、更好懂？我們把資料準備（WITH）跟主查詢（SELECT）分開。就像先把桌子整理乾淨再開始工作——呼吸都順暢了。

範例 2：多個 CTE

你可以在一個查詢裡定義多個 CTE。這在你需要分階段準備資料時超好用。

給定：成績 表格，存放學生各課程的分數：

任務：找出每個學生的平均分數，再選出平均分數大於 85 的學生。

多個 CTE 的解法：

WITH 學生平均 AS (
    SELECT 學生編號, AVG(分數) AS 平均分數
    FROM 成績
    GROUP BY 學生編號
),
高分學生 AS (
    SELECT 學生編號, 平均分數
    FROM 學生平均 	-- 用第一個 CTE - 學生平均
    WHERE 平均分數 > 85
)

SELECT *
FROM 高分學生; -- 用第二個 CTE - 高分學生

這裡：

1. 學生平均 準備好每個學生的平均分數。
2. 高分學生 用這些資料選出分數大於 85 的學生。

CTE 跟子查詢的差別

先說重點：CTE 不是拿來取代子查詢的，但有時候它真的比較方便。

子查詢就是查詢裡面再包一個查詢。臨時要查資料很方便，但一多起來，code 會變成一團亂。

範例：

SELECT *
FROM (
    SELECT 學生編號, AVG(分數) AS 平均分數
    FROM 成績
    GROUP BY 學生編號
) AS 學生平均
WHERE 平均分數 > 85;

子查詢可以放在 SELECT、FROM、WHERE、HAVING 裡面。還可以引用外層查詢的欄位。CTE 在這點就比較有限。

CTE 讓 code 超好讀，也比較不容易出錯。你不用一直巢狀查詢，只要「命名」子查詢的結果，後面就能一直用。

WITH 學生平均 AS (
    SELECT 學生編號, AVG(分數) AS 平均分數
    FROM 成績
    GROUP BY 學生編號
)

SELECT *
FROM 學生平均
WHERE 平均分數 > 85;

CTE 超適合在一個查詢裡多次用到同一批資料的時候。

什麼時候該用 CTE？

• 當你想把複雜查詢拆成幾個有邏輯的步驟。
• 如果查詢要好讀、好維護。沒人想看一堆像義大利麵一樣的巢狀 code。
• 臨時準備資料，只給這個查詢用。

最後一個範例：課程分析

來把今天學到的都用上：

1. 找出平均分數高的學生。
2. 列出他們的名字跟他們選的課程。

WITH 學生平均 AS (
    SELECT 學生編號, AVG(分數) AS 平均分數
    FROM 成績
    GROUP BY 學生編號
),
高分學生 AS (
    SELECT 學生編號
    FROM 學生平均
    WHERE 平均分數 > 85
),
學生課程 AS (
    SELECT e.學生編號, c.課程名稱
    FROM 選課 e
    JOIN 課程 c ON e.課程編號 = c.課程編號
)

SELECT ha.學生編號, sc.課程名稱
FROM 高分學生 ha
JOIN 學生課程 sc ON ha.學生編號 = sc.學生編號;

注意整個流程超有條理：

1. 先準備好平均分數。
2. 再選出高分學生。
3. 最後把他們跟課程連起來。

現在你已經可以正式用 CTE 寫出漂亮、好讀又強大的 SQL 查詢啦！

加油——去寫你自己的專案吧！