SQL CTE 全面解析：初學者的入門指南

Facebook LinkedIn Tweet

更新日期： 2025 年 3 月 25 日

在撰寫 SQL 查詢時，你是否曾因為子查詢太複雜、重複邏輯太多而感到頭痛？這時候，CTE（Common Table Expression）就是你的好幫手。

CTE 是一種在查詢中暫時命名結果集的方式，它可以讓 SQL 查詢更清楚、更容易維護，特別是在面對多層嵌套或遞迴需求時。

對於剛接觸 SQL 的你來說，學會 CTE 不僅能讓查詢語句更有結構，也能幫助你邁向進階資料處理的第一步。

本文將帶你一步步認識 CTE 的語法、用途與實務範例，讓你不再畏懼 SQL 中看似複雜的查詢邏輯。

CTE 的基本概念

CTE 是什麼？

CTE（Common Table Expression，共通資料表示式） 是一種在 SQL 中，定義暫時性結果集的語法結構。

它讓我們可以先建立一段可重複使用的查詢結果，再將這段結果拿來繼續查詢或處理。

CTE 並不會在資料庫中建立永久的表格，它只在目前的 SQL 執行期間短暫存在，並且通常用於簡化複雜查詢、提高語句的可讀性與維護性。

你可以把 CTE 想像成是一個 SQL 中的「暫時變數」，幫助你先把某段查詢結果定義出來，接著再使用這個「變數」來寫更清楚、更結構化的查詢語句。

舉例來說：

假設你想找出薪水高於公司平均薪資的員工，你可以先用 CTE 算出平均薪資，再用主查詢去比對每個員工的薪資是否高於平均。

這樣做比直接嵌套子查詢更清楚，也更容易後續維護。

CTE 的特性：

它只在單一查詢中有效，執行完就消失。
不能被永久儲存或重複使用在其他 SQL 查詢中（除非包在 View 或 Stored Procedure 裡）。
可以定義一個或多個 CTE，甚至可以彼此串接。
支援遞迴查詢，適合處理階層式資料（例如部門樹、分類結構）。

CTE 的語法結構

以下是 CTE 的基本語法：

WITH cte_name AS (
  SELECT ...
)
SELECT * FROM cte_name;

我們來逐一拆解這段語法：

元素	說明
WITH	關鍵字，表示這裡開始定義一個 CTE。
cte_name	CTE 的名稱，就像一個暫時表格的名稱，後續查詢會引用它。建議取具意義的名字。
AS	表示後面是這個 CTE 的查詢定義。
(SELECT ...)	CTE 的內容，這裡寫一個 SQL 查詢，會產生一個臨時的資料集。
SELECT * FROM cte_name	主查詢，這裡可以直接把剛剛定義的 cte_name 當作資料來源來查詢。

實際範例：

WITH avg_salary AS (
  SELECT AVG(salary) AS avg_sal FROM employees
)
SELECT name, salary
FROM employees, avg_salary
WHERE employees.salary > avg_salary.avg_sal;

說明：

我們用 CTE avg_salary 預先計算整體平均薪資，並存成一個臨時的資料表。
主查詢中可以直接使用這個 avg_salary 結果，來比對每位員工的薪資是否高於平均。
優點是整體邏輯清晰，也能避免在主查詢中寫重複的 AVG(salary) 子句。

小技巧與補充

命名習慣建議： CTE 名稱應該與其功能相關，像 monthly_sales、active_users、top_products，讓日後閱讀或維護更直覺。
多個 CTE 可串接使用： 你可以一次定義多個 CTE，只要用逗號隔開即可。
CTE 也可以搭配 INSERT、UPDATE、DELETE 使用（不是只能 SELECT！）

範例（多個 CTE）：

WITH dept_avg AS (
  SELECT department_id, AVG(salary) AS avg_sal
  FROM employees
  GROUP BY department_id
),
high_salary_employees AS (
  SELECT name, salary, department_id
  FROM employees
)
SELECT h.name, h.salary, d.avg_sal
FROM high_salary_employees h
JOIN dept_avg d ON h.department_id = d.department_id
WHERE h.salary > d.avg_sal;

這裡我們定義了兩個 CTE：

dept_avg：計算每個部門的平均薪資
high_salary_employees：列出所有員工基本資訊

主查詢最後做比對，找出薪水高於部門平均的員工。整體邏輯清楚、層次分明，也方便除錯與修改。

CTE 的應用場景

提高可讀性與維護性

在實務開發中，SQL 查詢往往不是一句簡單的 SELECT 就能完成。

當你需要計算平均值、做多層篩選、分組統計時，查詢語句常常會變得又長又複雜，尤其是使用巢狀子查詢（Subquery）時，更容易讓查詢變得難以閱讀與維護。

這時候，CTE 就派上用場了。

CTE 讓你可以將子查詢邏輯拉到查詢最上方定義，把查詢分成清楚的步驟。

這不只讓整體查詢語法更具可讀性，也讓團隊合作、後續除錯或修改變得更直觀。

✅ 範例：找出薪資高於全公司平均的員工

📌 使用子查詢的寫法：

SELECT name, salary
FROM employees
WHERE salary > (
  SELECT AVG(salary)
  FROM employees
);

這樣的寫法雖然正確，但當查詢越來越長時，這種巢狀結構容易混淆。而且如果未來還要重複使用平均薪資的結果，就得再寫一次子查詢。

📌 使用 CTE 的寫法：

WITH avg_salary AS (
  SELECT AVG(salary) AS avg_sal
  FROM employees
)
SELECT name, salary
FROM employees, avg_salary
WHERE salary > avg_salary.avg_sal;

這裡我們將平均薪資的計算邏輯抽出來，寫在 CTE avg_salary 中，讓整個查詢分成兩個明確階段：

先算出平均薪資。
再找出薪資高於平均的員工。

這種寫法更有結構、更容易理解與修改，尤其當這段查詢放進大型專案或報表中時，這種清楚的架構能大幅減少維護成本。

重複使用中間結果

在某些情況下，我們會在查詢中重複使用相同的資料集或計算邏輯，例如：

計算每個部門的平均薪資後，還想再跟這些平均薪資做比對。
找出符合某些條件的客戶名單，並在不同條件下使用相同的客戶清單去做分析。

這時，如果使用子查詢，就必須把這段邏輯重複寫好幾次，不僅冗長，也容易出錯。

✅ CTE 的優勢就在於它就像變數一樣，可以將這段邏輯寫一次、用很多次。

範例：計算部門平均薪資後，同時做兩種分析

WITH dept_avg AS (
  SELECT department_id, AVG(salary) AS avg_sal
  FROM employees
  GROUP BY department_id
)
SELECT * FROM dept_avg WHERE avg_sal > 60000;

-- 如果你後續還需要這份資料，例如比對某個人的薪資是否高於部門平均
SELECT e.name, e.salary, d.avg_sal
FROM employees e
JOIN dept_avg d ON e.department_id = d.department_id
WHERE e.salary > d.avg_sal;

我們定義了一個 CTE dept_avg，它只寫一次，卻可以被兩次查詢重複引用，不用再寫重複的 GROUP BY 和 AVG 計算。

這就是 CTE 的可重用性大優勢，讓你避免重複自己、讓 SQL 更乾淨、好讀又高效。

遞迴查詢（進階應用）

這是 CTE 最強大的功能之一，也是子查詢與 JOIN 難以處理的情境：遞迴查詢（Recursive Query）。

什麼是遞迴查詢？就是資料具有「階層」結構，例如：

組織圖（員工 → 上司 → 上上司）
商品分類（主類別 → 次類別 → 子類別）
網站目錄（根目錄 → 子目錄 → 子子目錄）

這種資料層層相連，每一層都指向上一層或下一層，傳統 SQL 很難寫。但透過 CTE 的遞迴功能，我們可以輕鬆處理這類問題。

📌 遞迴 CTE 的語法結構：

WITH RECURSIVE cte_name AS (
  -- Anchor：遞迴的起點（最上層節點）
  SELECT ... FROM table WHERE condition

  UNION ALL

  -- Recursive：遞迴邏輯，往下找下一層
  SELECT ... FROM cte_name JOIN table ON ...
)
SELECT * FROM cte_name;

✅ 範例：找出某員工的所有下屬（假設有上下屬關係的欄位）

WITH RECURSIVE employee_hierarchy AS (
  -- Anchor：找出最上層的主管（例如員工 ID = 1）
  SELECT id, name, manager_id
  FROM employees
  WHERE id = 1

  UNION ALL

  -- Recursive：找出他的下屬 → 然後再找出下屬的下屬
  SELECT e.id, e.name, e.manager_id
  FROM employees e
  JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM employee_hierarchy;

這段查詢會找出某位主管（id = 1）底下的所有下屬，不管幾層，從直屬員工到最底層的成員，全都能一網打盡。

如果不用遞迴 CTE，這種查詢需要非常複雜的 UNION 或多層 JOIN 結構才能做到，非常麻煩。

CTE vs 子查詢 vs 暫時表格：三種 SQL 技術的比較與選擇

當你在撰寫 SQL 查詢時，經常會遇到「中間結果需要暫時存起來再用」的情境，例如：

想先算出某個平均值，再根據這個值做過濾。
想先整理某些資料，再從中進一步統計或比對。
想避免在查詢中重複寫相同的邏輯。

在這些情況下，我們通常會考慮使用 CTE（Common Table Expression）、子查詢（Subquery） 或 暫時表格（Temporary Table）。

這三種方式看起來都能達到類似的效果，但在實際使用上，其特性與適用場景差異很大。

以下我們用表格和實際說明，來幫你快速掌握三者之間的差別。

特性比較表

特性	CTE（Common Table Expression）	子查詢（Subquery）	暫時表格（Temporary Table）
可讀性	高，邏輯清晰、結構分明	低，巢狀語法易混淆	中，需額外語法與命名
重複使用	✅ 可重複使用一段邏輯	❌ 無法重複使用	✅ 可在多段查詢中使用
維護性	高，易於修改、易除錯	低，錯誤難追蹤	中，維護較依賴命名與上下文
效能表現	視資料庫引擎與寫法而定	一般，若嵌套過多效能會下降	效能佳，適合大型資料處理
支援遞迴查詢	✅ 支援 WITH RECURSIVE	❌ 不支援	❌ 不支援
使用難度	中等，需理解語法結構	初階，入門時常見使用方式	偏高，需理解建立與釋放流程

三者深入說明與適用情境

✅ 1. CTE：適合結構化查詢、多次重複使用邏輯

CTE 的最大優點是可讀性高與邏輯清晰，非常適合當查詢邏輯比較複雜時使用。

例如：

一個查詢中需要多步邏輯處理（先分類、再統計、再篩選）
中間資料集會在後面重複用到
團隊開發中，程式碼清晰能讓協作更順利
需要遞迴處理階層資料（如組織架構）

🔧 建議使用情境：

報表邏輯複雜、需要分層查詢
中間結果需要被多次引用
想讓查詢語法更結構化、便於維護

✅ 2. 子查詢：適合單一條件、快速處理簡單需求

子查詢是初學者最常使用的方式，常出現在 WHERE 或 SELECT 中，用來先做某個條件過濾或欄位運算。

雖然語法簡潔，但當查詢層層巢狀、或需要重複使用同一段邏輯時，就會變得冗長且難以維護。

🔧 建議使用情境：

單一條件過濾（如：薪水 > 平均薪水）
查詢結構簡單
不需重複使用中間結果

📌 範例比較：

使用子查詢：

SELECT name, salary
FROM employees
WHERE salary > (
  SELECT AVG(salary) FROM employees
);

使用 CTE：

WITH avg_salary AS (
  SELECT AVG(salary) AS avg_sal FROM employees
)
SELECT name, salary
FROM employees, avg_salary
WHERE salary > avg_salary.avg_sal;

✅ 結論：如果查詢邏輯日後會改動，建議用 CTE，維護起來會輕鬆許多。

✅ 3. 暫時表格：適合處理大量資料與跨多段查詢的場景

暫時表格是一種在資料庫中短暫建立的實體表格。

可以在整個資料庫連線期間被反覆使用，甚至可以在不同查詢之間共用，非常適合處理大量資料、進行分階段分析或儲存中間結果。

但缺點是：需要顯式建立與刪除、語法相對繁瑣、不易快速測試。

🔧 建議使用情境：

需處理非常大量的資料（數十萬筆以上）
跨多個 SQL 查詢共用中間資料集
效能要求高，且查詢邏輯複雜、分多步驟

📌 建立與使用範例：

CREATE TEMPORARY TABLE high_salary_emps AS
SELECT * FROM employees WHERE salary > 60000;

SELECT department_id, COUNT(*) 
FROM high_salary_emps
GROUP BY department_id;

DROP TEMPORARY TABLE high_salary_emps;

💡 小提醒：該怎麼選？

查詢需求	建議使用方式
單次查詢、邏輯簡單	子查詢
查詢邏輯需分多步、需重複使用中間資料	CTE
資料量大、需要跨查詢多次操作	暫時表格

使用 CTE 的注意事項

雖然 CTE 是一個功能強大、能提升查詢可讀性與彈性的工具，但在實際應用過程中，仍有幾個重要的使用細節需要注意。

了解這些細節，能幫助你更正確地使用 CTE，也能避免效能瓶頸或維護上的麻煩。

命名清晰：CTE 名稱應簡潔、有意義

雖然 CTE 名稱只是暫時性的，但取一個清楚、具描述性的名字非常重要。因為你可能會在主查詢中多次引用這個名稱，或是與其他資料表 JOIN，因此命名混亂會讓查詢難以閱讀，也增加維護成本。

✅ 命名建議：

避免用 cte1, tmp, result 這種模糊不清的命名。
使用描述性命名，例如：monthly_sales_summary, high_salary_employees, dept_avg_salary。
命名風格一致，例如：全小寫 + 底線命名（snake_case）是常見慣例。

清楚的命名不只是方便你自己，也是對團隊成員、未來的你的一種負責。

一次性有效：CTE 只在單一查詢語句中有效

CTE 是一個只在當前查詢中有效的暫時性資料集。這表示：

CTE 無法跨查詢重複使用。
無法在後續查詢中直接引用已定義的 CTE（除非重新定義一次）。
執行完該查詢後，CTE 的內容就會自動消失，不會存在於資料庫中。

這點常讓初學者誤會，以為可以像暫存表格一樣重複使用或保留在 session 中。事實上，CTE 的作用範圍只在它所屬的那一條 SQL 語句中。

當然可以！以下是擴寫後的「五、使用 CTE 的注意事項」完整段落，針對每個小點都提供詳細說明、背景補充與實務建議，幫助初學者在使用 CTE 時避開常見錯誤，寫出更穩定、更有效率的查詢語句：

效能考量：CTE 並非在所有資料庫中都比較快

雖然 CTE 的結構更清晰，但在某些資料庫系統中，它的效能表現不一定比子查詢或暫存表更好。

這一點在處理大型資料集或高複雜查詢時，特別需要注意。

🚨 常見的效能陷阱：

在 MySQL（尤其是 8.0 以下版本） 中，CTE 預設為 物化（Materialized），也就是資料庫會先執行整個 CTE 查詢並儲存結果，再給主查詢使用。這可能導致 不必要的記憶體與磁碟操作。
有些資料庫無法針對 CTE 做良好的查詢優化（如索引使用不佳），反而讓整體效能下降。
遞迴 CTE 若處理不當，會造成性能瓶頸甚至系統資源耗盡。

✅ 實務建議：

在正式環境使用 CTE 前，務必進行效能測試（例如使用 EXPLAIN 或 ANALYZE 檢查查詢計劃）。
若資料量龐大，且 CTE 中邏輯會被重複使用，考慮改為暫時表或資料快取機制。
若使用的是 PostgreSQL，效能通常會較好，因其對 CTE 與遞迴處理支援完整；但在 MySQL 或 SQL Server 上則需特別小心。

補充提醒：CTE 雖好，但不是萬能

CTE 絕對是寫 SQL 查詢的重要利器，但它並不是每一種場景的最佳解。它的強項在於：

簡化多步查詢邏輯
提高可讀性與結構清晰度
支援遞迴處理階層資料

但如果：

查詢只會執行一次、邏輯很簡單 → 子查詢會更快速。
需要跨查詢反覆使用 → 暫存表更合適。
效能是最重要的考量 → 需要根據資料庫特性仔細選擇。

使用 CTE 就像寫程式時定義函式一樣，能讓你把查詢邏輯模組化、易讀化，是寫出「好維護的 SQL」不可或缺的技巧。

但就像工具箱中的每一種工具一樣，CTE 是非常好用的一把利器，但要用在對的地方，才能發揮最大價值。

在開始使用 CTE 前，請牢記三件事：

名稱要清楚，結構要乾淨。
它只在當次查詢有效，不會被保留。
效能不是絕對好，要看資料庫與資料量。

掌握這些關鍵原則，讓你在使用 CTE 時更加穩健、自信，寫出更專業、可維護的 SQL 查詢！

結語：讓 CTE 成為你寫 SQL 的秘密武器

CTE 雖然看起來是進階語法，但其實是讓你寫出更清楚、更乾淨、更好維護的查詢語句的絕佳工具。

只要你掌握基本語法、學會分拆查詢邏輯，CTE 很快就會成為你在處理資料時不可或缺的好幫手。

不管你是剛開始學 SQL，還是想進一步優化查詢表現，現在就是開始使用 CTE 的最好時機。

SQL CTE 全面解析：初學者的入門指南

CTE 的基本概念

CTE 是什麼？

CTE 的語法結構

小技巧與補充

CTE 的應用場景

提高可讀性與維護性

重複使用中間結果

遞迴查詢（進階應用）

CTE vs 子查詢 vs 暫時表格：三種 SQL 技術的比較與選擇

特性比較表

三者深入說明與適用情境

✅ 1. CTE：適合結構化查詢、多次重複使用邏輯

✅ 2. 子查詢：適合單一條件、快速處理簡單需求

✅ 3. 暫時表格：適合處理大量資料與跨多段查詢的場景

使用 CTE 的注意事項

命名清晰：CTE 名稱應簡潔、有意義

一次性有效：CTE 只在單一查詢語句中有效

效能考量：CTE 並非在所有資料庫中都比較快

補充提醒：CTE 雖好，但不是萬能

結語：讓 CTE 成為你寫 SQL 的秘密武器

S3 與 Postgres 的比較與應用

S3 儲存桶標籤（Tags）簡單介紹：讓你的 S3 管理更輕鬆！

SQL CHECK 約束：確保數據有效性的關鍵機制

GraphQL 如何根據 Query 動態生成 SQL？

MongoDB 入門指南：靈活高效的 NoSQL 資料庫

初學者入門：認識 GraphQL Schema

關於本站

CTE 的基本概念

CTE 是什麼？

CTE 的語法結構

小技巧與補充

CTE 的應用場景

提高可讀性與維護性

重複使用中間結果

遞迴查詢（進階應用）

CTE vs 子查詢 vs 暫時表格：三種 SQL 技術的比較與選擇

特性比較表

三者深入說明與適用情境

✅ 1. CTE：適合結構化查詢、多次重複使用邏輯

✅ 2. 子查詢：適合單一條件、快速處理簡單需求

✅ 3. 暫時表格：適合處理大量資料與跨多段查詢的場景

使用 CTE 的注意事項

命名清晰：CTE 名稱應簡潔、有意義

一次性有效：CTE 只在單一查詢語句中有效

效能考量：CTE 並非在所有資料庫中都比較快

補充提醒：CTE 雖好，但不是萬能

結語：讓 CTE 成為你寫 SQL 的秘密武器

Similar Posts

關於本站