SQL中的WITH語句（公共表表達式CTE）

WITH語句，也稱為公共表表達式（Common Table Expression，CTE），是SQL中一種強大的功能，它允許你創建臨時結果集，這些結果集可以在后續的查詢中被引用。

基本語法

WITH cte_name AS (SELECT column1, column2, ...FROM table_nameWHERE condition
)
SELECT * FROM cte_name;

主要特點

1. 臨時結果集：CTE只在當前查詢執行期間存在
2. 可讀性：使復雜查詢更易于理解和維護
3. 遞歸能力：支持遞歸查詢（使用WITH RECURSIVE）

使用場景

1. 簡化復雜查詢

WITH sales_summary AS (SELECT product_id, SUM(quantity) as total_soldFROM salesGROUP 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. 替代子查詢

-- 使用子查詢
SELECT * FROM (SELECT employee_id, salary FROM employees
) AS emp_data;-- 使用CTE更清晰
WITH emp_data AS (SELECT employee_id, salary FROM employees
)
SELECT * FROM emp_data;

3. 遞歸查詢（WITH RECURSIVE）

WITH RECURSIVE employee_hierarchy AS (-- 基礎查詢（起點）SELECT employee_id, name, manager_id, 1 as levelFROM employeesWHERE manager_id IS NULLUNION ALL-- 遞歸部分SELECT e.employee_id, e.name, e.manager_id, eh.level + 1FROM employees eJOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy;

多個CTE

可以定義多個CTE，用逗號分隔：

WITH 
cte1 AS (SELECT ...),
cte2 AS (SELECT ...),
cte3 AS (SELECT ...)
SELECT ... FROM cte1 JOIN cte2 ON ... JOIN cte3 ON ...;

優點

1. 提高查詢可讀性和可維護性
2. 避免重復子查詢
3. 支持遞歸查詢
4. 可以在同一查詢中多次引用

注意事項

1. CTE只在當前查詢中有效
2. 不同數據庫對CTE的支持可能略有不同
3. 復雜的遞歸CTE可能導致性能問題

WITH語句是SQL中組織復雜查詢的強大工具，特別適用于需要多次引用相同子查詢或需要遞歸處理層次結構數據的場景。