在實際的數據庫開發和分析中，我們常常會遇到復雜的多層嵌套查詢，這樣的 SQL 語句不僅難以閱讀，也容易出錯。

這時候就需要使用一個非常實用又優雅的關鍵字 —— WITH！

它可以幫助我們將復雜的子查詢提取出來并命名，從而提升代碼可讀性、復用性和維護性。這個功能也被稱為 CTE（Common Table Expressions，公用表表達式）。

---

?1.什么是 WITH？

WITH 是 SQL 中用于定義臨時結果集的關鍵字。這些臨時結果集可以在后續查詢中像普通表一樣被引用，并且只在當前查詢執行期間存在。

你可以把它理解為：“先寫好一個中間結果，后面可以直接拿來用”。

---

?2.基本語法

WITH cte_name AS (-- 子查詢內容SELECT ...
)
-- 后續主查詢中使用 cte_name
SELECT * FROM cte_name;

• cte_name?是你給中間結果集起的名字。
• 可以定義多個 CTE，用逗號分隔。

---

?3.示例講解

假設我們有一個 orders 表，記錄了訂單信息：

order_id	customer_id	amount
1	1	2999
2	2	499
3	1	199
4	3	899

---

?示例1：計算每個客戶的訂單總金額（簡單 CTE 使用）

WITH customer_totals AS (SELECT customer_id, SUM(amount) AS total_amountFROM ordersGROUP BY customer_id
)
SELECT *
FROM customer_totals
WHERE total_amount > 500;

結果：

customer_id	total_amount
1	3198
3	899

👉 這里我們先定義了一個 CTE customer_totals 來計算每位客戶的總消費金額，然后主查詢篩選出金額大于500的客戶。

---

?示例2：多個 CTE 的使用（分步處理復雜邏輯）

WITH
-- 第一步：統計每位客戶的總消費
customer_totals AS (SELECT customer_id, SUM(amount) AS total_amountFROM ordersGROUP BY customer_id
),
-- 第二步：根據總消費劃分客戶等級
customer_levels AS (SELECT customer_id, total_amount,CASEWHEN total_amount > 1000 THEN '高級客戶'WHEN total_amount BETWEEN 500 AND 1000 THEN '中級客戶'ELSE '普通客戶'END AS levelFROM customer_totals
)
-- 最終查詢：展示客戶等級信息
SELECT * FROM customer_levels;

結果：

customer_id	total_amount	level
1	3198	高級客戶
2	499	普通客戶
3	899	中級客戶

?通過多個 CTE 分步驟處理，整個查詢邏輯更加清晰易懂。

---

?示例3：遞歸 CTE（以員工層級為例）

遞歸 CTE 是 WITH 的一種高級用法，常用于處理樹形結構或層級數據（如組織架構、分類目錄等）。

假設我們有一個 employees 表：

employee_id	name	manager_id
1	張三	NULL
2	李四	1
3	王五	2

WITH RECURSIVE employee_hierarchy AS (-- 初始查詢：沒有上級的員工（即 CEO）SELECT employee_id, name, manager_id, CAST(name AS TEXT) AS hierarchy_pathFROM employeesWHERE manager_id IS NULLUNION ALL-- 遞歸部分：查找下屬員工SELECT e.employee_id, e.name, e.manager_id,CAST(eh.hierarchy_path || ' → ' || e.name AS TEXT)FROM employees eINNER JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy;

結果：

employee_id	name	manager_id	hierarchy_path
1	張三	NULL	張三
2	李四	1	張三 → 李四
3	王五	2	張三 → 李四 → 王五

?這個例子展示了如何用遞歸 CTE 構建一個組織層級路徑，非常適合處理樹狀結構數據。

---

對比項	使用?WITH（CTE）	不使用 CTE（嵌套子查詢）
可讀性	更高，結構清晰	較低，嵌套多層時難讀
復用性	可多次引用	每次都要重復寫
調試方便性	易于單獨測試每個 CTE	難以調試嵌套部分
遞歸支持	支持（RECURSIVE）	不支持
性能	與子查詢基本一致，但邏輯優化后可能更好	視具體實現而定

---

4. 總結對比表

功能	SQL 示例
定義單個 CTE	WITH cte AS (...) SELECT * FROM cte;
定義多個 CTE	WITH a AS (...), b AS (...) SELECT * FROM a JOIN b...
遞歸 CTE	WITH RECURSIVE ...
提高代碼可讀性	將復雜查詢拆分為多個邏輯塊
支持重用	同一查詢中可多次引用 CTE 名