課程 18：Common Table Expression (CTE)

學會使用 Common Table Expression（CTE）簡化 SQL 查詢，透過 WITH 語法實作提升可讀性與可維護性，適合進階學習者。

一、什麼是 Common Table Expression (CTE)？

• CTE 是一個臨時的結果集，可以在單個 SQL 語句中被多次引用。
• 它使用 WITH 關鍵字來定義。
• CTE 只在定義它的 SQL 語句執行期間存在，不會永久儲存。

二、SQL為什麼使用 CTE？

• 提高可讀性：將複雜查詢分解成更小的、可管理的區塊。
• 簡化複雜聯接和子查詢：讓多層嵌套的查詢更易於理解。
• 遞歸查詢：CTE 支援定義遞歸關係（例如組織結構圖或物料清單）。
• 在同一查詢中多次引用結果集：避免重複撰寫相同的子查詢。

三、CTE 語法

WITH cte_name (column1, column2, ...) AS (
    -- CTE 查詢定義 (SELECT 語句)
    SELECT column1, column2, ...
    FROM your_table
    WHERE condition
)
-- 主查詢，引用 CTE
SELECT *
FROM cte_name
WHERE condition;

四、CTE 範例

假設有一個 sales 表 (product_id, sale_amount)，我們想找出銷售額高於平均銷售額的產品。

WITH AverageSales AS (
    SELECT AVG(sale_amount) AS avg_amt
    FROM sales
)
SELECT s.product_id, s.sale_amount
FROM sales s
JOIN AverageSales a ON s.sale_amount > a.avg_amt;

這個例子中，AverageSales 是一個 CTE，它計算出平均銷售額，然後主查詢使用 JOIN 將 sales 表與 AverageSales CTE 連接，找出銷售額高於平均的產品。

五、SQL常見錯誤

• CTE 沒有緊跟在 WITH 關鍵字之後。
• 在同一語句中定義多個 CTE 時，CTE 之間沒有用逗號分隔。
• 主查詢試圖在 WITH 語句塊之外引用 CTE。
• 遞歸 CTE 沒有終止條件。

六、SQL課後小練習

1. 假設有一個 employees 表 (id, name, salary, department_id)，請使用 CTE 找出每個部門的最高薪水，並列出所有薪水等於該部門最高薪水的員工。