MSSQL 公用暫時表格CTE(Common Table Expression)

在 SQL Server（MSSQL）中，CTE 是「Common Table Expression」的縮寫，中文常翻作「共用表達式」或「公用暫時表格」。它是一種在查詢中定義暫時結果集的方式，讓你可以寫出更清晰、模組化的 SQL。

使用時機:

 

📘 CTE 的用途與優點

功能	說明
✅ 結構清晰	把複雜查詢拆成多段，讓邏輯更容易閱讀與維護
✅ 可重複使用	可以定義多個 CTE，彼此串接使用
✅ 支援遞迴	可用於階層資料（如組織樹、分類樹）的遞迴查詢
✅ 可搭配 INSERT/UPDATE/DELETE	MSSQL 支援在 DML 操作中使用 CTE

🧪 基本語法範例

sql

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

你也可以定義多個 CTE：

sql

WITH cte1 AS (...),
     cte2 AS (...)
SELECT ...
FROM cte1
JOIN cte2 ON ...

🧠 與暫存表的差異

類型	是否寫入磁碟	是否可重複使用	是否支援索引
CTE	❌（記憶體中）	✅（僅限一次查詢）	❌
暫存表（#table）	✅	✅	✅（可加索引）

🕰️ 使用 CTE 的最佳時機

1. 簡化複雜查詢邏輯

• 當查詢包含多層子查詢或 JOIN，使用 CTE 可以將每一層邏輯分段處理。

• 例如：先計算銷售總額，再根據總額篩選 VIP 客戶。

2. 遞迴查詢（階層資料）

• 查詢樹狀結構如部門上下層、分類目錄、員工主管關係。

• CTE 是 SQL 中唯一支援遞迴的語法（在 MSSQL、PostgreSQL、Oracle 中皆支援）。

sql

WITH RecursiveCTE AS (
  SELECT id, parent_id, name
  FROM categories
  WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.parent_id, c.name
  FROM categories c
  JOIN RecursiveCTE r ON c.parent_id = r.id
)
SELECT * FROM RecursiveCTE;

3. 重複使用中間結果

• 當某個查詢結果需要在後續多次使用時，CTE 可以避免重複撰寫 SQL。

4. 改善可讀性與維護性

• 對於多人協作或日後維護的 SQL，CTE 能讓邏輯更清楚，減少錯誤。

5. 搭配 INSERT/UPDATE/DELETE 使用

• MSSQL 支援在 DML 操作中使用 CTE，讓你可以先篩選資料再進行更新或刪除。

sql

WITH TargetRows AS (
  SELECT *
  FROM orders
  WHERE status = 'pending'
)
DELETE FROM TargetRows;