上一篇我們學了 CTE 的語法和好處，知道它可以讓巢狀子查詢變乾淨。
但上一篇的範例比較簡單，子查詢只有兩層，你可能覺得「好像還好啊，沒那麼難讀」。
這篇文章要帶你做一個比較麻煩的實戰任務——組出一張業務績效報表。
讓你體會 CTE 在真實場景中到底有多好用。
情境說明：線上課程平台的業務績效
假設我們在一個線上課程平台工作。
平台有一群銷售人員（業務），他們會打電話給潛在客戶，介紹課程、邀請試聽，最終希望客戶簽約成為學生。
為了評估每個業務的表現，主管需要看一張績效報表，上面有這些指標：
- 通時：這個業務總共打了多久的電話（通話時間總長）
- 通次：這個業務總共打了幾通電話（通話次數）
- 邀約數：成功邀請了幾個客戶來聽 Demo
- Demo 數：實際執行了幾場 Demo（展示課程）
- 成交數：最終簽約成交了幾筆
- 退簽數：成交之後又退費的有幾筆
通時和通次看的是業務的努力程度。
邀約數和 Demo 數看的是努力有沒有轉換成實際行動。
成交數看的是最終成果。
退簽數則是品質指標——如果成交很多但退簽也很多，代表業務可能給了不切實際的承諾，這不是我們想要的。
資料表結構
要組出這張報表，我們需要用到以下幾張表。
會員表 與 身份組表
會員表存放的是平台上所有使用者的資料，包括學生、潛在客戶，以及銷售人員。
業務之所以也放在會員表裡，是因為他們同樣需要登入系統操作後台，在系統的角度上也算是一種「會員」。
而且不論身份為何，每個人都有姓名、Email 這些共用欄位，放在同一張表裡是合理的。
至於如何區分不同身份的使用者，靠的是另一張身份組表。
身份組表裡定義了「銷售人員」、「學生」、「潛在客戶」等分類，每個會員都會透過 身份組ID 對應到其中一個身份組。
這種設計的好處在於：未來如果要做權限控管（例如學生只能看前台、業務可以進後台），直接根據身份組來判斷就好。
會員聯絡表
會員聯絡表記錄的是業務與客戶之間的通話紀錄。
主要欄位包含：會員 ID（通話對象）、建立人 ID（發起通話的業務）、類型（撥出或接聽）、時間長度，以及描述（通話內容摘要）。
會員任務表
會員任務表記錄的是業務與客戶之間約定的任務，例如邀約 Demo（課程展示）。
主要欄位包含：會員 ID（任務對象）、建立者 ID（建立任務的人，通常是業務）、執行者 ID（實際執行任務的人）、類型（邀約、Demo 等）。
這裡有一個需要特別注意的地方：建立者和執行者不一定是同一個人。
業務負責打電話邀約客戶來聽 Demo，所以「建立者」通常就是那位業務。
但實際進行 Demo 的人不一定是他本人。
舉例來說，如果客戶的程度比較進階，問的問題比較深入，業務可能回答不了，這時候就會請更資深的業務或授課老師來主持 Demo。
以上面的表格為例，第二筆任務是 Kevin（ID=4）邀約了 Amy（ID=3）來聽 Demo，但實際執行 Demo 的是 Jason（ID=1）。
這代表 Kevin 負責邀約，Jason 負責展示，兩個人在這筆任務中扮演不同的角色。
因此在後面的統計中，邀約數要看「建立者ID」——誰發起了這個邀約；Demo 數要看「執行者ID」——誰實際完成了這場展示。
會員合約表
會員合約表記錄的是簽約資訊。
主要欄位包含：會員 ID（簽約的客戶）、建立者 ID（促成簽約的業務）、金額、簽署時間，以及撤簽時間。
判斷邏輯是這樣的：如果簽署時間不是 NULL，代表這筆合約已經成交。
如果簽署時間和撤簽時間都不是 NULL，則代表成交之後客戶又申請了退費。
各指標的定義
在動手寫 SQL 之前，先把每個指標的定義搞清楚。
首先是衡量業務努力程度的兩個指標。
通時的計算方式是從會員聯絡表中，把該業務所有通話的時間長度加總起來（SUM）。
這個數字代表他總共花了多少時間在電話上。
通次同樣來自會員聯絡表，計算該業務有幾筆紀錄（COUNT）。
這個數字代表他總共打了幾通電話。
接著是衡量轉換效率的兩個指標。
邀約數的資料來源是會員任務表。
篩選條件有兩個：類型是 Demo，而且建立者是該業務。
符合條件的紀錄有幾筆，邀約數就是多少（COUNT）。
Demo 數也是從會員任務表，但篩選的是執行者是該業務的紀錄。
邀約數一定會大於或等於 Demo 數，因為客戶答應了不一定會出現，而且實際做 Demo 的人也不一定是邀約的那位業務。
最後是衡量成果與品質的兩個指標。
成交數是從會員合約表中，篩選出建立者是該業務、且簽署時間不是 NULL 的紀錄，計算筆數。
退簽數的篩選條件幾乎一樣，只是多加一個條件：撤簽時間也不是 NULL，代表這筆合約成交之後又被退費了。
整體看下來，每個指標的邏輯都很簡單，就是篩選加上 COUNT 或 SUM。
真正的挑戰不在運算本身，而是怎麼把這六個分別來自不同表的結果整合在一起，還要寫得清楚好讀。
用 CTE 一步一步組出來
有了 CTE，我們的做法完全不同。
不再是「先想主查詢長什麼樣，再把子查詢塞回去」，而是反過來：先把每一塊子查詢寫好、取好名字，最後再用主查詢把它們組合起來。
而且每寫完一塊，你都可以馬上在底下用 SELECT * FROM 別名 來檢查結果對不對，不用等到全部寫完才能測試。
第一步：組出業務名單
第一件事，先確認「誰是業務」。
我們不會想用整張會員大表來跑，所以先把業務篩出來：
WITH
業務 AS (
SELECT 會員.ID AS 業務ID, 會員.名稱
FROM 會員
JOIN 身份組
ON 會員.身份組ID = 身份組.ID
WHERE 身份組.名稱 = '銷售人員'
),注意這裡我把 會員.ID 取了一個別名叫 業務ID。
這很重要，因為後面所有的統計子查詢都會有一個 業務ID 欄位，到時候合併的時候就可以用 USING(業務ID) 來簡化寫法，不用每次都寫 ON A.業務ID = B.業務ID。
統一欄位名稱是一個小動作，但對後面的撰寫幫助很大。
這段子查詢跑出來的結果會像這樣：
從整張會員大表中，只留下身份組是「銷售人員」的兩筆資料，而且欄位名稱已經從 ID 改成了 業務ID。
第二步：算聯絡統計（通時、通次）
用業務表去 LEFT JOIN 會員聯絡表，根據業務 ID 做分組，算出每個業務的通話次數和通話時間總長：
聯絡統計 AS (
SELECT
業務.業務ID,
COUNT(*) AS 通次,
SUM(會員聯絡.時間長度) AS 通時
FROM 業務
LEFT JOIN 會員聯絡
ON 業務.業務ID = 會員聯絡.建立人ID
GROUP BY 業務.業務ID
),你發現了嗎？因為前面已經把「業務」這個子查詢取好名字了，這裡直接引用就好，不用再寫一次篩選業務的邏輯。
這段子查詢跑出來的結果會像這樣：
Jason（ID=1）打了 2 通電話，總共 25 分鐘；Kevin（ID=4）打了 1 通，共 20 分鐘。
第三步：算邀約統計
從會員任務表中，篩選類型是 Demo、且建立者是該業務的紀錄：
邀約統計 AS (
SELECT
業務.業務ID,
COUNT(*) AS 邀約數
FROM 業務
LEFT JOIN 會員任務
ON 業務.業務ID = 會員任務.建立者ID
WHERE 會員任務.類型 = 'demo'
GROUP BY 業務.業務ID
),這裡的關鍵是 ON 的條件用的是「建立者ID」，因為邀約這個動作是由業務發起的。
跑出來的結果：
對照會員任務表，Jason（ID=1）建立了第 1、3 筆任務，所以邀約數是 2；Kevin（ID=4）建立了第 2 筆，邀約數是 1。
第四步：算 Demo 統計
跟邀約統計很像，但 ON 的條件不同——這裡看的是執行者，不是建立者：
Demo統計 AS (
SELECT
業務.業務ID,
COUNT(*) AS Demo數
FROM 業務
LEFT JOIN 會員任務
ON 業務.業務ID = 會員任務.執行者ID
WHERE 會員任務.類型 = 'demo'
GROUP BY 業務.業務ID
),跑出來的結果：
對照會員任務表，Jason（ID=1）是第 1、2 筆的執行者，Demo 數是 2；Kevin（ID=4）是第 3 筆的執行者，Demo 數是 1。
邀約統計和 Demo 統計雖然都是從會員任務表來的，但因為 ON 的欄位不同（一個是建立者、一個是執行者），本質上就是不同的統計結果，所以分開寫是合理的，不需要硬合併。
第五步：算成交統計
從會員合約表中，篩選建立者是該業務、且簽署時間不是 NULL 的紀錄：
成交統計 AS (
SELECT
業務.業務ID,
COUNT(*) AS 成交數
FROM 業務
LEFT JOIN 會員合約
ON 業務.業務ID = 會員合約.建立者ID
WHERE 會員合約.簽署時間 IS NOT NULL
GROUP BY 業務.業務ID
),跑出來的結果：
對照會員合約表，Jason（ID=1）建立的合約中，第 1 筆有簽署時間，第 3 筆沒有（客戶還沒簽），所以成交數是 1。
Kevin（ID=4）建立的第 2 筆合約有簽署時間，成交數也是 1。
第六步：算退簽統計
跟成交統計幾乎一樣，只是多一個篩選條件——撤簽時間也不是 NULL：
退簽統計 AS (
SELECT
業務.業務ID,
COUNT(*) AS 退簽數
FROM 業務
LEFT JOIN 會員合約
ON 業務.業務ID = 會員合約.建立者ID
WHERE 會員合約.簽署時間 IS NOT NULL
AND 會員合約.撤簽時間 IS NOT NULL
GROUP BY 業務.業務ID
)注意，這是最後一個子查詢，所以右括號後面不加逗號。
跑出來的結果：
只有 Kevin（ID=4）的第 2 筆合約同時有簽署時間和撤簽時間，代表這筆成交後來退費了。
Jason 的合約沒有任何退簽紀錄，所以他不會出現在這張表裡。
最後：主查詢把所有結果合併
SELECT
業務.業務ID,
業務.名稱,
聯絡統計.通時,
聯絡統計.通次,
邀約統計.邀約數,
Demo統計.Demo數,
成交統計.成交數,
退簽統計.退簽數
FROM 業務
LEFT JOIN 聯絡統計 USING(業務ID)
LEFT JOIN 邀約統計 USING(業務ID)
LEFT JOIN Demo統計 USING(業務ID)
LEFT JOIN 成交統計 USING(業務ID)
LEFT JOIN 退簽統計 USING(業務ID)主查詢就這麼短，一看就懂。
全部都是以業務 ID 為基準做 LEFT JOIN，把各項統計結果兜在一起。
最終跑出來的結果：
Jason 的退簽數是 NULL，因為他在退簽統計中沒有任何紀錄，LEFT JOIN 之後自然就是 NULL。
這就是我們用 CTE 一步一步組出來的完整業務績效報表。
如果不用 CTE，會有多痛苦？
前面我們用 CTE 寫出來的查詢，結構清楚、每一塊各司其職。
但如果不用 CTE，把所有子查詢直接塞進主查詢的括號裡面呢？
在比較之前，先補充一個觀念：巢狀結構有兩種讓人崩潰的方式。
一種是很「深」——子查詢裡面還有子查詢，一層包一層，越包越深。
另一種是很「廣」——主查詢裡面並排放了很多子查詢，橫向展開，一路 JOIN 下去。
我們這次遇到的就是「廣」的那種：主查詢裡面並排塞了六個子查詢。
兩種都很難讀，混在一起更是災難。
接下來直接把兩種寫法放在一起，你感受一下差異。
CTE 寫法
WITH
業務 AS (
SELECT 會員.ID AS 業務ID, 會員.名稱
FROM 會員
JOIN 身份組
ON 會員.身份組ID = 身份組.ID
WHERE 身份組.名稱 = '銷售人員'
),
聯絡統計 AS (
SELECT 業務.業務ID, COUNT(*) AS 通次, SUM(會員聯絡.時間長度) AS 通時
FROM 業務
LEFT JOIN 會員聯絡 ON 業務.業務ID = 會員聯絡.建立人ID
GROUP BY 業務.業務ID
),
邀約統計 AS (
SELECT 業務.業務ID, COUNT(*) AS 邀約數
FROM 業務
LEFT JOIN 會員任務 ON 業務.業務ID = 會員任務.建立者ID
WHERE 會員任務.類型 = 'demo'
GROUP BY 業務.業務ID
),
Demo統計 AS (
SELECT 業務.業務ID, COUNT(*) AS Demo數
FROM 業務
LEFT JOIN 會員任務 ON 業務.業務ID = 會員任務.執行者ID
WHERE 會員任務.類型 = 'demo'
GROUP BY 業務.業務ID
),
成交統計 AS (
SELECT 業務.業務ID, COUNT(*) AS 成交數
FROM 業務
LEFT JOIN 會員合約 ON 業務.業務ID = 會員合約.建立者ID
WHERE 會員合約.簽署時間 IS NOT NULL
GROUP BY 業務.業務ID
),
退簽統計 AS (
SELECT 業務.業務ID, COUNT(*) AS 退簽數
FROM 業務
LEFT JOIN 會員合約 ON 業務.業務ID = 會員合約.建立者ID
WHERE 會員合約.簽署時間 IS NOT NULL
AND 會員合約.撤簽時間 IS NOT NULL
GROUP BY 業務.業務ID
)
SELECT
業務.業務ID, 業務.名稱,
聯絡統計.通時, 聯絡統計.通次,
邀約統計.邀約數, Demo統計.Demo數,
成交統計.成交數, 退簽統計.退簽數
FROM 業務
LEFT JOIN 聯絡統計 USING(業務ID)
LEFT JOIN 邀約統計 USING(業務ID)
LEFT JOIN Demo統計 USING(業務ID)
LEFT JOIN 成交統計 USING(業務ID)
LEFT JOIN 退簽統計 USING(業務ID)你會發現，光看最底下的主查詢就能讀懂整段 SQL 在做什麼。
每個別名都清清楚楚：業務、聯絡統計、邀約統計……一目了然。
如果某個數字算錯了，你只要回到對應的 CTE 區塊去檢查就好。
巢狀子查詢寫法
SELECT
業務.業務ID, 業務.名稱,
聯絡統計.通時, 聯絡統計.通次,
邀約統計.邀約數, Demo統計.Demo數,
成交統計.成交數, 退簽統計.退簽數
FROM (
SELECT 會員.ID AS 業務ID, 會員.名稱
FROM 會員
JOIN 身份組 ON 會員.身份組ID = 身份組.ID
WHERE 身份組.名稱 = '銷售人員'
) AS 業務
LEFT JOIN (
SELECT 會員.ID AS 業務ID, COUNT(*) AS 通次, SUM(會員聯絡.時間長度) AS 通時
FROM 會員
JOIN 身份組 ON 會員.身份組ID = 身份組.ID
LEFT JOIN 會員聯絡 ON 會員.ID = 會員聯絡.建立人ID
WHERE 身份組.名稱 = '銷售人員'
GROUP BY 會員.ID
) AS 聯絡統計 ON 業務.業務ID = 聯絡統計.業務ID
LEFT JOIN (
SELECT 會員.ID AS 業務ID, COUNT(*) AS 邀約數
FROM 會員
JOIN 身份組 ON 會員.身份組ID = 身份組.ID
LEFT JOIN 會員任務 ON 會員.ID = 會員任務.建立者ID
WHERE 身份組.名稱 = '銷售人員' AND 會員任務.類型 = 'demo'
GROUP BY 會員.ID
) AS 邀約統計 ON 業務.業務ID = 邀約統計.業務ID
LEFT JOIN (
SELECT 會員.ID AS 業務ID, COUNT(*) AS Demo數
FROM 會員
JOIN 身份組 ON 會員.身份組ID = 身份組.ID
LEFT JOIN 會員任務 ON 會員.ID = 會員任務.執行者ID
WHERE 身份組.名稱 = '銷售人員' AND 會員任務.類型 = 'demo'
GROUP BY 會員.ID
) AS Demo統計 ON 業務.業務ID = Demo統計.業務ID
LEFT JOIN (
SELECT 會員.ID AS 業務ID, COUNT(*) AS 成交數
FROM 會員
JOIN 身份組 ON 會員.身份組ID = 身份組.ID
LEFT JOIN 會員合約 ON 會員.ID = 會員合約.建立者ID
WHERE 身份組.名稱 = '銷售人員' AND 會員合約.簽署時間 IS NOT NULL
GROUP BY 會員.ID
) AS 成交統計 ON 業務.業務ID = 成交統計.業務ID
LEFT JOIN (
SELECT 會員.ID AS 業務ID, COUNT(*) AS 退簽數
FROM 會員
JOIN 身份組 ON 會員.身份組ID = 身份組.ID
LEFT JOIN 會員合約 ON 會員.ID = 會員合約.建立者ID
WHERE 身份組.名稱 = '銷售人員'
AND 會員合約.簽署時間 IS NOT NULL
AND 會員合約.撤簽時間 IS NOT NULL
GROUP BY 會員.ID
) AS 退簽統計 ON 業務.業務ID = 退簽統計.業務ID你感受到了嗎？
整段 SQL 是一大塊，每個 LEFT JOIN 後面都跟著一整段子查詢。
而且因為沒辦法引用前面已經算好的「業務」名單，每一段子查詢都得重複寫 JOIN 身份組 ... WHERE 身份組.名稱 = '銷售人員'。
光是「篩選業務」這個邏輯就出現了六次。
如果哪天身份組的名稱從「銷售人員」改成「業務」，你得找到六個地方一起改，漏掉任何一個都會出錯。
這就是為什麼我們推薦用 CTE——不只是好讀，更是好維護。
為什麼用 LEFT JOIN？
這裡全部用 LEFT JOIN 是有原因的。
不是每個業務都有打電話，不是每個業務都有邀約成功，不是每個業務都有成交。
如果你用 INNER JOIN，那些沒有某項紀錄的業務就會從結果中消失。
用 LEFT JOIN 的話，沒有對應資料的欄位就會顯示 NULL，這才是我們要的——我們想看到所有業務的完整報表，哪怕某些欄位是空的。
另外提一個情境：如果你想要先把所有統計子查詢的結果合成一張大的統計表，再跟業務表做 LEFT JOIN，那統計表之間的合併就要用 FULL OUTER JOIN（完全外部合併），否則會漏掉某些業務的資料。
不過最簡單的做法就是像上面一樣，全部以業務表為主，一路 LEFT JOIN 下去，乾淨俐落。
進階思考：子查詢要拆到多細？
你有沒有注意到，成交統計和退簽統計的結構幾乎一樣？
它們都是從會員合約表篩選出來的，只是篩選條件不同。
這時候你可以考慮再拆一層，先把「業務合約」這個共用的基底抽出來：
業務合約 AS (
SELECT *
FROM 業務
LEFT JOIN 會員合約
ON 業務.業務ID = 會員合約.建立者ID
WHERE 會員合約.簽署時間 IS NOT NULL
),
成交統計 AS (
SELECT 業務ID, COUNT(*) AS 成交數
FROM 業務合約
GROUP BY 業務ID
),
退簽統計 AS (
SELECT 業務ID, COUNT(*) AS 退簽數
FROM 業務合約
WHERE 撤簽時間 IS NOT NULL
GROUP BY 業務ID
)這樣成交統計和退簽統計都是基於「業務合約」這個已經篩選好的結果，不用重複寫 JOIN 和第一層篩選。
而且如果未來主管又要求新的指標（例如「合約金額總計」），你只要再加一個子查詢，一樣引用「業務合約」就好了，完全不用動前面的邏輯。
不過，到底要拆到多細，這其實是一門藝術。
拆太細，子查詢太多，反而增加閱讀負擔。
拆太粗，共用的邏輯重複出現，維護起來很痛苦。
判斷的依據是你對業務邏輯的了解程度。
如果你很確定某個統計的算法不會變（例如通時就是把時間加總，不會有其他算法），那直接在一個子查詢裡算完就好，不需要再拆。
但如果你知道某張表的資料未來會被反覆用在不同的統計（例如合約表可能要算成交數、退簽數、金額總計、平均金額……），那就值得先把基底資料抽出來，後面的統計各自引用。
這就像寫程式裡面 Functional Programming 的概念：把每一個轉換步驟拆成小零件，需要的時候可以自由組合，也方便個別微調和測試。
CTE 寫作的實際流程與注意事項
實際在寫 CTE 的時候，建議按照以下流程進行。
第一步，先寫第一個子查詢（通常是最基礎的那張表，例如「業務」），然後在底下直接 SELECT * FROM 業務，看結果對不對。
第二步，確認沒問題後，接著寫第二個子查詢（例如「聯絡統計」），然後底下改成 SELECT * FROM 聯絡統計，再看一次結果。
第三步，一個一個加上去，每寫完一塊就驗證一次。
最後，所有子查詢都確認沒問題了，再寫主查詢把它們全部組合起來。
這個流程的好處是：不需要把整段 SQL 全部寫完才能測試。
每一個小區塊都可以獨立驗證，出錯的時候馬上就知道是哪一塊有問題。
如果是用巢狀結構，要從一大坨 SQL 裡面抽出某一段來測試，光是找到對應的括號就要花不少時間。
另外補充一個容易搞混的地方：CTE 裡面的這些名稱（業務、聯絡統計、邀約統計……），正確的叫法是「別名（Alias）」，不是「變數（Variable）」。
雖然它的行為跟變數很像——先宣告、再引用——但在資料庫的世界裡，有些系統允許在自訂函數裡面寫真正的變數。
為了避免混淆，CTE 裡面取的名字一律叫「別名」。
小結
這篇文章的重點整理如下：
CTE 在實戰中的最大價值，是讓你可以「先把每一塊子查詢獨立寫好、測試好，最後再用簡潔的主查詢組合起來」。
當報表需要從很多張表撈資料並合併時，用 CTE 可以避免寫出又深又廣的巢狀結構。
統一欄位名稱（例如都叫 業務ID）可以讓後續合併時使用 USING 語法，寫起來更簡潔。
全部用 LEFT JOIN 可以確保沒有任何統計紀錄的業務不會從結果中消失。
重複的子查詢結構可以再拆出一層共用的基底，讓程式碼更 DRY（Don’t Repeat Yourself）。
子查詢要拆到多細，取決於你對業務邏輯的了解——越可能被重複使用的部分，越值得獨立出來。
CTE 裡面的名稱叫「別名」不叫「變數」，這是為了跟資料庫中真正的變數做區分。