CTE 通用資料表運算式 (SQL Common Table Expression)

CTE (Common Table Expression，通用資料表運算式) 是一種暫時性的命名結果集，它只在該 SQL 語句執行期間存在。CTE 讓你可以先定義一個「虛擬資料表」，然後在後續的查詢中像使用普通資料表一樣參照它。

使用 CTE 可以讓複雜的 SQL 查詢更模組化、更易讀、更容易維護。

WITH 語法 (Syntax)

WITH cte_name AS (
    SELECT column_list
    FROM table_name
    WHERE condition
)
SELECT * FROM cte_name;

也可以為 CTE 指定欄位名稱：

WITH cte_name (column1, column2) AS (
    SELECT col_a, col_b
    FROM table_name
)
SELECT * FROM cte_name;

CTE 基本用法範例 (Example)

employees 資料表：

範例： 使用 CTE 先計算各部門平均薪資，再找出薪資高於部門平均的員工。

WITH dept_avg AS (
    SELECT Dept, AVG(Salary) AS AvgSalary
    FROM employees
    GROUP BY Dept
)
SELECT e.Name, e.Dept, e.Salary, d.AvgSalary
FROM employees e
JOIN dept_avg d ON e.Dept = d.Dept
WHERE e.Salary > d.AvgSalary;

結果：

多個 CTE

可以在同一個 WITH 子句中定義多個 CTE，用逗號分隔：

WITH
    cte1 AS (
        SELECT Dept, AVG(Salary) AS AvgSalary
        FROM employees
        GROUP BY Dept
    ),
    cte2 AS (
        SELECT Dept, COUNT(*) AS EmpCount
        FROM employees
        GROUP BY Dept
    )
SELECT cte1.Dept, cte1.AvgSalary, cte2.EmpCount
FROM cte1
JOIN cte2 ON cte1.Dept = cte2.Dept;

後面的 CTE 可以參照前面定義的 CTE：

WITH
    sales_by_region AS (
        SELECT Region, SUM(Amount) AS TotalSales
        FROM sales
        GROUP BY Region
    ),
    top_regions AS (
        SELECT Region, TotalSales
        FROM sales_by_region
        WHERE TotalSales > 100000
    )
SELECT * FROM top_regions;

CTE vs 子查詢

相較於子查詢 (Subquery)，CTE 有以下優點：

使用子查詢：

SELECT e.Name, e.Salary, d.AvgSalary
FROM employees e
JOIN (
    SELECT Dept, AVG(Salary) AS AvgSalary
    FROM employees
    GROUP BY Dept
) d ON e.Dept = d.Dept
WHERE e.Salary > d.AvgSalary;

使用 CTE：

WITH dept_avg AS (
    SELECT Dept, AVG(Salary) AS AvgSalary
    FROM employees
    GROUP BY Dept
)
SELECT e.Name, e.Salary, d.AvgSalary
FROM employees e
JOIN dept_avg d ON e.Dept = d.Dept
WHERE e.Salary > d.AvgSalary;

CTE 的使用情境

1. 簡化複雜查詢

將複雜查詢拆分成多個邏輯步驟：

WITH
    -- 步驟 1：計算每月銷售
    monthly_sales AS (...),
    -- 步驟 2：計算移動平均
    moving_avg AS (...),
    -- 步驟 3：識別異常值
    anomalies AS (...)
SELECT * FROM anomalies;

2. 避免重複計算

當同一個子查詢需要被多次使用時：

WITH sales_summary AS (
    SELECT Product, SUM(Amount) AS Total
    FROM sales
    GROUP BY Product
)
SELECT
    s1.Product,
    s1.Total,
    s1.Total * 100.0 / (SELECT SUM(Total) FROM sales_summary) AS Percentage
FROM sales_summary s1;

3. 遞迴查詢

處理階層式資料，如組織架構、分類目錄等。詳見 Recursive CTE。

4. 資料轉換管道

建立資料處理的步驟流程：

WITH
    raw_data AS (SELECT ...),
    cleaned_data AS (SELECT ... FROM raw_data WHERE ...),
    aggregated_data AS (SELECT ... FROM cleaned_data GROUP BY ...)
SELECT * FROM aggregated_data;

CTE 的限制

1. 僅在當前語句有效：CTE 只存在於它所定義的那個 SQL 語句中。
2. 不能建立索引：CTE 是虛擬的，無法在其上建立索引。
3. 可能影響效能：某些資料庫會將 CTE 實體化 (materialize)，某些則會內聯展開。

資料庫支援

相關主題

• Recursive CTE - 遞迴查詢
• 子查詢 - 巢狀查詢
• VIEW - 持久化的虛擬資料表