WITH 子句（也稱為公共表表達式，Common Table Expression，簡稱 CTE）是 SQL 中一種強大的查詢構建工具，它可以顯著提高復雜查詢的可讀性和可維護性。
一、基本語法結構
WITH cte_name AS (SELECT ... -- 定義CTE的查詢
)
SELECT ... FROM cte_name; -- 主查詢使用CTE
二、CTE 的核心特點
- 臨時結果集：CTE 只在當前查詢執行期間存在
- 可引用性：定義后可在主查詢中多次引用
- 作用域限制：僅在緊隨其后的單個語句中有效
三、MySQL 中 CTE 的具體用法
1. 基本 CTE（單表表達式）
WITH sales_summary AS (SELECT product_id, SUM(quantity) AS total_soldFROM ordersGROUP BY product_id
)
SELECT p.product_name, s.total_sold
FROM products p
JOIN sales_summary s ON p.product_id = s.product_id;
2. 多 CTE 定義（逗號分隔）
WITH
customer_orders AS (SELECT customer_id, COUNT(*) AS order_countFROM ordersGROUP BY customer_id
),
high_value_customers AS (SELECT customer_idFROM customer_ordersWHERE order_count > 5
)
SELECT c.customer_name
FROM customers c
JOIN high_value_customers h ON c.customer_id = h.customer_id;
3. 遞歸 CTE（MySQL 8.0+ 支持）
遞歸 CTE 用于處理層次結構數據：
WITH RECURSIVE org_hierarchy AS (-- 基礎查詢（錨成員）SELECT id, name, parent_id, 1 AS levelFROM organizationWHERE parent_id IS NULLUNION ALL-- 遞歸查詢（遞歸成員）SELECT o.id, o.name, o.parent_id, h.level + 1FROM organization oJOIN org_hierarchy h ON o.parent_id = h.id
)
SELECT * FROM org_hierarchy;
四、CTE 的優勢
-
提高可讀性：
- 將復雜查詢分解為邏輯塊
- 類似編程中的變量定義
-
避免重復子查詢：
-- 不使用CTE（重復子查詢） SELECT * FROM (SELECT ... FROM table1) AS t1 JOIN (SELECT ... FROM table1) AS t2...-- 使用CTE（避免重復） WITH t1 AS (SELECT ... FROM table1) SELECT * FROM t1 JOIN t1 AS t2... -
支持遞歸查詢：處理樹形/層次結構數據
五、CTE 與臨時表的區別
| 特性 | CTE | 臨時表 |
|---|---|---|
| 生命周期 | 僅當前語句有效 | 會話結束前有效 |
| 存儲 | 不物理存儲 | 可能存儲在內存或磁盤 |
| 索引 | 不能創建索引 | 可以創建索引 |
| 可見性 | 僅定義它的查詢可見 | 同一會話的后續查詢可見 |
| 性能 | 優化器可能內聯展開 | 需要實際創建和填充 |
六、實際應用場景
1. 復雜報表查詢
WITH
monthly_sales AS (...),
product_ranking AS (...)
SELECT ... FROM monthly_sales JOIN product_ranking...
2. 數據清洗管道
WITH
raw_data AS (...),
cleaned_data AS (...),
enriched_data AS (...)
SELECT * FROM enriched_data;
3. 層次結構遍歷（組織架構、評論線程等）
WITH RECURSIVE comment_tree AS (...)
SELECT * FROM comment_tree;
七、性能注意事項
-
物化提示：
WITH cte_name AS (SELECT /*+ MATERIALIZE */ ... -- 強制物化 ) -
合并提示：
WITH cte_name AS (SELECT /*+ MERGE */ ... -- 強制合并到主查詢 ) -
遞歸深度控制（MySQL 默認 1000）：
SET @@cte_max_recursion_depth = 2000;
八、版本兼容性
- MySQL 8.0+ 完整支持 CTE 和遞歸 CTE
- MySQL 5.7 及更早版本不支持 CTE
WITH 子句是現代 SQL 開發中不可或缺的工具，合理使用可以大幅提升查詢的清晰度和維護性，特別是在處理多層嵌套或遞歸數據時。
)
