公用表表達式（Common Table Expression，簡稱CTE）是SQL中一個強大的功能，它可以幫助開發者提高查詢的可讀性和可維護性。CTE允許您在同一個查詢中定義臨時結果集，便于復雜查詢的構建和管理。本文將深入探討CTE的定義、語法及其應用實例，幫助您更好地利用這一功能。

一、什么是CTE？

CTE是一種臨時結果集，它在執行查詢時可被引用。與子查詢相比，CTE可以提高查詢的清晰度和可讀性，特別是在處理復雜邏輯時。

1. CTE的基本結構

CTE的基本結構包括三個部分：

• WITH關鍵字：用于定義CTE。
• CTE名稱：給定一個唯一的名稱，以便后續引用。
• 查詢定義：定義CTE所包含的查詢。

語法示例如下：

WITH cte_name AS (
    SELECT column1, column2
    FROM table_name
    WHERE condition
)
SELECT * FROM cte_name;

二、CTE的基本使用方法

CTE的使用方法相對簡單，下面將通過幾個實例來展示其應用場景。

1. 簡單的CTE示例

假設我們有一個員工表employees，我們希望查詢所有部門的員工數量?？梢允褂肅TE來實現：

WITH DepartmentCount AS (
    SELECT department_id, COUNT(*) AS employee_count
    FROM employees
    GROUP BY department_id
)
SELECT * FROM DepartmentCount;

在這個示例中，CTEDepartmentCount計算了每個部門的員工數量，然后主查詢從CTE中選擇結果。

2. 多層CTE

CTE可以嵌套使用，允許更復雜的查詢。例如，我們想查詢每個部門的平均薪資及其員工數量：

WITH AvgSalary AS (
    SELECT department_id, AVG(salary) AS average_salary
    FROM employees
    GROUP BY department_id
),
EmployeeCount AS (
    SELECT department_id, COUNT(*) AS employee_count
    FROM employees
    GROUP BY department_id
)
SELECT a.department_id, a.average_salary, e.employee_count
FROM AvgSalary a
JOIN EmployeeCount e ON a.department_id = e.department_id;

在這個例子中，兩個CTE分別計算了部門的平均薪資和員工數量，最后在主查詢中合并結果。

三、CTE的優勢

1. 提高可讀性

CTE使得復雜查詢的邏輯更加清晰，便于后期維護。例如，通過使用有意義的CTE名稱，可以快速理解查詢的目的和結果。

2. 避免重復代碼

在某些情況下，CTE可以避免在多個地方重復編寫相同的子查詢，減少了代碼的冗余。

3. 支持遞歸查詢

CTE還支持遞歸查詢，適合處理層級結構數據，如組織結構或目錄樹。示例如下：

WITH RECURSIVE EmployeeHierarchy AS (
    SELECT employee_id, manager_id, employee_name
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.manager_id, e.employee_name
    FROM employees e
    INNER JOIN EmployeeHierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM EmployeeHierarchy;

這個示例展示了如何使用遞歸CTE查詢整個員工層級結構。

四、總結

公用表表達式（CTE）是SQL中的一項重要功能，它不僅可以提高查詢的可讀性和可維護性，還支持遞歸查詢，適合處理復雜數據結構。通過合理使用CTE，開發者可以編寫出更清晰、更高效的SQL查詢。希望本文能夠幫助您理解CTE的基本概念及其實際應用。