MySQL WITH 子句徹底解析｜從初學者到實務應用的進階範例

1 1. 簡介
2 2. WITH 子句（共通表格式）的基礎知識
3 3. MySQL WITH 子句的基本用法
4 4. 遞迴 WITH 子句的應用範例
5 5. 使用 WITH 子句時的注意事項
6 6. 實務中的應用範例
7 7. 常見問題 (FAQ)
8 8. 總結

1. 簡介

MySQL 是許多開發者和資料庫管理員使用的資料庫管理系統，提供強大且靈活的 SQL 功能。其中，MySQL 8.0 引入的WITH 子句（共通表格式，Common Table Expression: CTE）是使 SQL 查詢更容易閱讀並提升維護性的強大工具。

本文將針對初學者到中級者，詳細說明此WITH 子句從基礎到應用的內容。特別介紹子查詢的替換和遞迴查詢的實作等實用內容。

對於正在學習 SQL 的人或在日常業務中為查詢效率化而煩惱的人，本文旨在提供具體的解決方案。請依循以下內容，理解 WITH 子句的基礎，並應用於實務中。

2. WITH 子句（共通表格式）的基礎知識

什麼是 WITH 子句？

WITH 子句是用來在 SQL 查詢中定義臨時結果集（共通表格式、CTE），並在後續查詢中使用它的語法。從 MySQL 8.0 開始支援，能將複雜的子查詢替換成簡潔且易懂的形式。

例如，如果直接寫子查詢，可讀性會降低，整個查詢容易變長。使用 WITH 子句，就能將查詢分成邏輯區塊，更容易理解。

WITH 子句的基本語法

以下是 WITH 子句的基本語法。

WITH 表格名稱 AS (
  SELECT 欄位1, 欄位2
  FROM 原始表格
  WHERE 條件
)
SELECT 欄位1, 欄位2
FROM 表格名稱;

在這個語法中，在 WITH 之後定義虛擬表格（共通表格式），並在主查詢中使用它。藉此，能簡潔表達重複使用的子查詢。

與子查詢或檢視的差異

WITH 子句是用來建立暫時可用的結果集，與子查詢或檢視在幾個方面有所不同。

特徵	WITH 子句	子查詢	檢視
範圍	僅在查詢內有效	僅能在定義的位置使用	可在整個資料庫中重複使用
暫時性	暫時的	暫時的	永久的
用途	簡化複雜查詢	臨時資料擷取	頻繁重複使用的資料擷取

WITH 子句比子查詢有更高的可讀性，且在不需要像檢視那樣建立永久物件時最適合。

使用 WITH 子句的優點

提升查詢的可讀性
即使有多個子查詢，使用 WITH 子句整理也能使結構更明確。
提升重用性
透過定義臨時結果集，能在查詢中多次參照。
提升維護性
能將查詢邏輯分割，因此修正或擴充更容易。

3. MySQL WITH 子句的基本用法

子查詢的替換

WITH 子句是一種用來簡化複雜子查詢的強大工具。如果直接嵌入子查詢，整個查詢會變得複雜且難以閱讀，但使用 WITH 子句可以提升可讀性。

以下是使用 WITH 子句替換子查詢的基本範例。使用子查詢的情況：

SELECT AVG(sales.total) AS average_sales
FROM (
  SELECT SUM(amount) AS total
  FROM orders
  GROUP BY customer_id
) AS sales;

使用 WITH 子句的情況：

WITH sales AS (
  SELECT SUM(amount) AS total
  FROM orders
  GROUP BY customer_id
)
SELECT AVG(sales.total) AS average_sales
FROM sales;

在這個範例中，使用 WITH 子句定義名為 sales 的臨時結果集，並在主要查詢中使用它。這樣，整個查詢就變得更容易理解且井然有序。

多個共通表格式（CTE）的定義

WITH 子句可以定義多個 CTE。這樣可以進一步將複雜查詢模組化。範例：

WITH 
  sales_per_customer AS (
    SELECT customer_id, SUM(amount) AS total_sales
    FROM orders
    GROUP BY customer_id
  ),
  high_value_customers AS (
    SELECT customer_id
    FROM sales_per_customer
    WHERE total_sales > 10000
  )
SELECT customer_id
FROM high_value_customers;

在這個範例中，使用 sales_per_customer 計算每個客戶的銷售總額，然後基於此使用 high_value_customers 抽出高額購買客戶。透過依序使用多個 CTE，可以逐步建構查詢。

巢狀 CTE 的使用方法

透過使用巢狀 CTE，可以進行更複雜的資料操作。範例：

WITH 
  sales_data AS (
    SELECT product_id, SUM(amount) AS total_sales
    FROM orders
    GROUP BY product_id
  ),
  ranked_sales AS (
    SELECT product_id, total_sales,
           RANK() OVER (ORDER BY total_sales DESC) AS rank
    FROM sales_data
  )
SELECT product_id, total_sales
FROM ranked_sales
WHERE rank <= 5;

在這個查詢中，使用 sales_data 彙總每個商品的銷售額，然後使用 ranked_sales 進行銷售額排名。最後，抽出前 5 名的商品。

實務中基本用法要點

將查詢分段思考
透過逐步建構 CTE，可以使查詢更容易閱讀，並簡化除錯。
儲存臨時計算結果
將多次使用的計算結果或篩選條件彙整到 CTE 中，可以減少程式碼的重複。
大規模資料的注意事項
由於 CTE 會產生臨時結果集，在處理大量資料時，需要考慮效能影響。

4. 遞迴 WITH 子句的應用範例

什麼是遞迴 WITH 子句？

遞迴 WITH 子句（遞迴 CTE）是使用共通表格式來自我參照地重複執行查詢，以處理階層資料或重複計算的方法。遞迴 CTE 在 MySQL 8.0 及後續版本中得到支援，特別適合處理具有親子關係或階層結構的資料。

遞迴 CTE 的基本語法

在定義遞迴 CTE 時，使用 WITH RECURSIVE 關鍵字。基本語法如下：

WITH RECURSIVE 遞迴表格名稱 AS (
  初始查詢 -- 遞迴的起始點
  UNION ALL
  遞迴查詢 -- 遞迴呼叫的查詢
)
SELECT * FROM 遞迴表格名稱;

初始查詢: 取得遞迴處理的第一個資料集。
遞迴查詢: 基於初始查詢或前次結果產生新資料。
UNION ALL: 結合初始查詢與遞迴查詢的結果。

處理階層資料的範例

遞迴 CTE 可用於展開具有階層結構的資料（例如：組織樹或類別樹）。範例：展開員工的管理階層以下以 employees 表格為例：

employee_id	name	manager_id
1	Alice	NULL
2	Bob	1
3	Charlie	1
4	David	2

使用此資料，建立以某員工為起點的全階層查詢。

WITH RECURSIVE employee_hierarchy AS (
  -- 初始查詢：取得頂層員工
  SELECT employee_id, name, manager_id, 1 AS level
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- 遞迴查詢：取得直屬部屬
  SELECT e.employee_id, e.name, e.manager_id, eh.level + 1
  FROM employees e
  INNER JOIN employee_hierarchy eh
  ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy;

結果：

employee_id	name	manager_id	level
1	Alice	NULL	1
2	Bob	1	2
3	Charlie	1	2
4	David	2	3

此查詢會遞迴地基於 manager_id 搜尋部屬，並展開整個階層。

遞迴 CTE 的限制與注意事項

需要遞迴結束條件
若遞迴查詢未滿足結束條件，可能會發生無限迴圈。請設定適當條件以避免無限迴圈。
對效能的影響
遞迴 CTE 涉及大量資料時會進行許多計算，因此查詢執行時間可能變長。請活用 LIMIT 子句或篩選條件來提升效率。
遞迴深度限制
MySQL 對遞迴深度有限制，若進行極深遞迴處理需特別注意。此限制可透過 max_recursive_iterations 參數設定。

活用遞迴 CTE 的情境

資料夾結構的遍歷: 遞迴搜尋資料夾與子資料夾。
組織圖的建立: 視覺化從上司到部屬的階層。
類別樹的顯示: 取得商品類別或標籤的階層。

遞迴 CTE 是這些情境中用來簡潔描述 SQL 查詢並提升可讀性的強大工具。

5. 使用 WITH 子句時的注意事項

對效能的影響與最佳化

CTE 的重新計算
使用 WITH 子句定義的 CTE，基本上每次查詢被參照時都會重新計算。因此，如果多次使用相同的 CTE，查詢的執行時間可能會變長。 範例：

   WITH sales AS (
     SELECT product_id, SUM(amount) AS total_sales
     FROM orders
     GROUP BY product_id
   )
   SELECT * FROM sales WHERE total_sales > 1000;
   SELECT COUNT(*) FROM sales;

上述情況下，sales 被參照兩次，因此會進行兩次計算。要防止此情況，當多次參照時，將結果儲存到暫存表格的方法很有效。解決方案：

   CREATE TEMPORARY TABLE temp_sales AS
   SELECT product_id, SUM(amount) AS total_sales
   FROM orders
   GROUP BY product_id;

   SELECT * FROM temp_sales WHERE total_sales > 1000;
   SELECT COUNT(*) FROM temp_sales;

複雜的 CTE 的分割
如果 WITH 子句過度巢狀化，整個查詢會變得複雜，偵錯會變得困難。有必要適當分割，以避免單一 CTE 內的處理過度複雜。

大量資料的使用

WITH 子句在執行中會產生暫時的資料集。處理大量資料時，這可能會對記憶體或儲存空間造成負擔。對策方法：

使用 WHERE 子句來篩選資料
透過在 CTE 內篩選不必要的資料，可以減少計算量。

  WITH filtered_orders AS (
    SELECT *
    FROM orders
    WHERE order_date > '2023-01-01'
  )
  SELECT customer_id, SUM(amount)
  FROM filtered_orders
  GROUP BY customer_id;

LIMIT 子句的活用
資料量多時，使用 LIMIT 子句來僅提取必要的資料。

MySQL 版本的相容性

MySQL 的 WITH 子句從 MySQL 8.0 開始支援。在之前的版本無法使用 WITH 子句，因此需要考慮替代手段。替代手段：

子查詢的使用
直接使用子查詢來代替 WITH 子句。

  SELECT AVG(total_sales)
  FROM (
    SELECT customer_id, SUM(amount) AS total_sales
    FROM orders
    GROUP BY customer_id
  ) AS sales;

建立檢視表
如果需要可重複使用的查詢，使用檢視表也很有效。

  CREATE VIEW sales_view AS
  SELECT customer_id, SUM(amount) AS total_sales
  FROM orders
  GROUP BY customer_id;

  SELECT AVG(total_sales) FROM sales_view;

WITH 子句的適當使用方式

重視可讀性
使用 WITH 子句的目的是整理查詢並提升可讀性。如果過度使用反而會使查詢複雜化，因此僅在必要時使用。
效能的驗證
確認執行計劃（EXPLAIN 指令），並検討最佳化效能的方法。

   EXPLAIN
   WITH sales AS (
     SELECT product_id, SUM(amount) AS total_sales
     FROM orders
     GROUP BY product_id
   )
   SELECT * FROM sales WHERE total_sales > 1000;

6. 實務中的應用範例

銷售資料的彙總

將銷售資料按月彙總，並利用該結果計算月平均銷售額的範例。範例：月別銷售的彙總與平均計算

WITH monthly_sales AS (
  SELECT 
    DATE_FORMAT(order_date, '%Y-%m') AS sales_month,
    SUM(amount) AS total_sales
  FROM orders
  GROUP BY sales_month
)
SELECT 
  sales_month, 
  total_sales,
  AVG(total_sales) OVER () AS average_sales
FROM monthly_sales;

在此查詢中，使用monthly_sales計算每月銷售額，並以此為基礎計算整體平均銷售額。藉此，可以清楚整理資料，並輕鬆進行分析。

基於特定條件的資料篩選

透過 WITH 子句分割伴隨複雜條件的篩選，可以提升可讀性。範例：高額購買客戶清單的建立

WITH customer_totals AS (
  SELECT 
    customer_id, 
    SUM(amount) AS total_spent
  FROM orders
  GROUP BY customer_id
)
SELECT 
  customer_id, 
  total_spent
FROM customer_totals
WHERE total_spent > 100000;

在此查詢中，使用customer_totals計算各客戶的購買總額，並從中抽出符合條件的高額購買客戶。

階層結構資料的分析

在分析組織或類別的階層資料時，再歸的 WITH 子句非常便利。範例：取得組織內直屬部下的清單

WITH RECURSIVE employee_hierarchy AS (
  SELECT 
    employee_id, 
    name, 
    manager_id, 
    1 AS level
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  SELECT 
    e.employee_id, 
    e.name, 
    e.manager_id, 
    eh.level + 1
  FROM employees e
  INNER JOIN employee_hierarchy eh
  ON e.manager_id = eh.employee_id
)
SELECT 
  employee_id, 
  name, 
  manager_id, 
  level
FROM employee_hierarchy
ORDER BY level, manager_id;

在此查詢中，使用employee_hierarchy建構階層資料，並依員工等級取得清單。藉此，可以動態產生組織圖等資訊。

活用多個共通表格式的分析

透過活用多個 WITH 子句，可以逐步處理資料，簡單實現複雜分析。範例：抽出商品類別別銷售上位商品

WITH category_sales AS (
  SELECT 
    category_id, 
    product_id, 
    SUM(amount) AS total_sales
  FROM orders
  GROUP BY category_id, product_id
),
ranked_sales AS (
  SELECT 
    category_id, 
    product_id, 
    total_sales,
    RANK() OVER (PARTITION BY category_id ORDER BY total_sales DESC) AS rank
  FROM category_sales
)
SELECT 
  category_id, 
  product_id, 
  total_sales
FROM ranked_sales
WHERE rank <= 3;

在此查詢中，計算各類別的銷售額，並抽出其中前 3 名的商品。在特定條件下篩選資料時非常有效。

實務應用時的要點

查詢的分割設計
活用 WITH 子句分割查詢，逐步處理資料，同時維持可讀性進行複雜分析。
僅抽出必要資料
活用 WHERE 子句或 LIMIT 子句，避免處理無用資料，從而實現高效的查詢設計。
業務中的彈性活用
銷售分析、客戶分群、庫存管理等，可依業務需求彈性活用。

7. 常見問題 (FAQ)

Q1: WITH 子句應該在什麼情況下使用？

A1:WITH 子句在以下類似情境中特別有效：

想要簡潔地描述複雜的子查詢時。
在多個查詢中重複使用相同資料集時。
想要邏輯地分割查詢以提升可讀性時。

例如，在需要多次使用相同彙總結果的查詢中，使用 WITH 子句可以更有效地處理。

Q2: 遞迴 CTE 在什麼情況下有幫助？

A2:遞迴 CTE 在需要階層結構或重複計算的情況下很有幫助。具體來說，在以下類似情境中使用：

階層資料的處理（例如：組織樹狀結構、類別結構的展開）。
資料夾或檔案的階層顯示。
數值或期間的逐次計算（例如：費波納契數列的計算）。

使用遞迴 CTE 可以輕鬆展開並處理自我參照的資料。

Q3: 使用 WITH 子句的查詢比檢視更有效率嗎？

A3:視情況而定。

WITH 子句會建立臨時的結果集，並僅在該查詢中使用。適合不需要頻繁重複使用的資料。
檢視會永久儲存在資料庫中，並可供其他查詢重複使用。適合重複使用的查詢。

根據需求選擇使用非常重要。

Q4: 使用 WITH 子句導致效能降低的原因是什麼？

A4:使用 WITH 子句時，主要的效能降低原因如下：

CTE 的重新計算：每次參照 WITH 子句的結果時都會重新計算，因此處理時間會增加。
大量資料的處理：在 WITH 子句中產生大量資料時，記憶體使用量會增加，導致效能降低。
缺少適當的索引：WITH 子句內的查詢缺少適當索引時，可能會導致處理速度變慢。

對策:

如果重複使用頻率高，則考慮使用臨時表格或檢視。
使用 WHERE 子句或 LIMIT 子句來適當篩選資料。

Q5: 在不支援 WITH 子句的 MySQL 版本中，有替代方法嗎？

A5:MySQL 8.0 之前的版本不支援 WITH 子句，因此使用以下替代方法：

使用子查詢
WITH 子句的替代，使用子查詢直接處理。

  SELECT AVG(total_sales)
  FROM (
    SELECT customer_id, SUM(amount) AS total_sales
    FROM orders
    GROUP BY customer_id
  ) AS sales;

使用臨時表格
將需要重複使用的資料集儲存到臨時表格中來應對。

  CREATE TEMPORARY TABLE temp_sales AS
  SELECT customer_id, SUM(amount) AS total_sales
  FROM orders
  GROUP BY customer_id;

  SELECT AVG(total_sales) FROM temp_sales;

Q6: 使用 WITH 子句的最佳實務是什麼？

A6:使用 WITH 子句時的最佳實務，請注意以下幾點：

重視簡潔性：不要強迫將複雜的查詢塞進 WITH 子句中，而是適當分割並整理。
驗證效能：確認執行計劃（EXPLAIN 指令），並視需要優化查詢。
考慮重複使用性：如果重複使用頻率高，則活用檢視或臨時表格。

8. 總結

本文中，針對 MySQL 8.0 引入的 WITH 子句（通用表格式，CTE），從基礎到應用廣泛說明。WITH 子句是用來使複雜查詢簡潔且易讀的非常便利的功能。以下總結本文的重要要點。

WITH 子句的主要優點

查詢的可讀性提升
透過簡潔整理複雜的子查詢，提升 SQL 程式碼的可讀性和維護性。
查詢的可重用性
在多次參照相同資料集的情況下，能夠有效率地處理。
可進行遞迴資料操作
活用遞迴 CTE，可以簡單處理階層資料或重複計算。

實務中的活用要點

銷售或客戶資料的分析 有助益，並能逐步彙總結果。
階層結構的資料處理（組織圖或類別結構等）可以使用遞迴 CTE 有效運用。
根據需要與檢視或暫存表格結合，即可實現靈活且高效的資料庫操作。

使用時的注意事項

WITH 子句雖然便利，但若不適當使用，可能導致效能降低。
考慮可重用性和效能，依個案考量檢視或暫存表格的使用區分。
確認執行計劃（EXPLAIN 指令）來驗證查詢的效率是很重要的。

下一步驟

WITH 子句的使用，即可建立更有效率且維護性高的 SQL 查詢。請務必在實際專案中試用。以下以這些步驟來實踐：

從簡單查詢開始，練習使用 WITH 子句的結構化。
使用遞迴 CTE 挑戰階層資料或複雜情境。
意識效能最佳化，進一步提升 SQL 技能。

以上，本文內容結束。請務必將 MySQL WITH 子句的知識應用在日常業務或學習中。