SQL中的CTE：完整指南及範例

如何創建 SQL CTE

在創建 CTE 時，我們使用 WITH 關鍵字來啟動 CTE 定義。CTE 的一般語法如下：

WITH cte_name (column1, column2, ...)
AS (
    -- 定義 CTE 的查詢
    SELECT ...
    FROM ...
    WHERE ...
)
-- 主查詢
SELECT ...
FROM cte_name;

Where:

• WITH：啟動CTE定義，表示接下來的名稱代表一個臨時結果集。

• cte_name：將此名稱指定給CTE，以在主查詢中引用它。

• 可選的列清單（column1，column2，…）：為CTE的結果集指定列名。當需要調整列名時，這很有用。

• 定義公用表達式的查詢：選擇數據並形成臨時結果集的內部查詢。

• 主查詢：通過其名稱引用公用表達式，像使用表格一樣。

讓我們看一個使用分層方法創建CTE的示例。假設我們有一個表Employees，我們希望創建一個CTE，選擇工資高於$50,000的員工。

步驟1：編寫基本查詢

我們首先編寫基本的SELECT查詢：

SELECT EmployeeID, FirstName, LastName, Salary
FROM Employees
WHERE Salary > 50000;

步驟2：使用WITH關鍵字包裹查詢以創建CTE

使用WITH關鍵字給CTE一個名字。

WITH HighEarningEmployees AS (
    SELECT EmployeeID, FirstName, LastName, Salary
    FROM Employees
    WHERE Salary > 50000
)

步驟3：在主查詢中使用CTE

最後，在SELECT語句中引用上面定義的CTE名稱來調用CTE。

-- 定義一個公共表達式（CTE）
WITH HighEarningEmployees AS (
    SELECT EmployeeID, FirstName, LastName, Salary
    FROM Employees
    WHERE Salary > 50000
)
-- 使用CTE選擇高收入員工
SELECT EmployeeID, FirstName, LastName
FROM HighEarningEmployees;

總結上述步驟，我們使用WITH關鍵字來定義名為HighEarningEmployees的CTE。內部查詢用於生成臨時數據集。主查詢引用HighEarningEmployees來顯示指定列EmployeeID、FirstName和LastName。

SQL CTE的用途是什麼

從上面的例子中，您可能會想知道為什麼我們在即使簡單的查詢也可以產生相同的結果時會使用CTEs。以下是原因：

簡化複雜的查詢

CTEs將複雜的SQL語句拆分為更小、更易管理的部分，使代碼更容易閱讀、編寫和維護。

假設我們有三個表：訂單、顧客和產品。我們想要找出每位在2024年購買的顧客所產生的總收入。當我們在不使用CTE的情況下編寫查詢時，看起來雜亂且難以閱讀和理解。

-- 選擇顧客名稱和他們訂單的總收入
SELECT c.CustomerName, SUM(p.Price * o.Quantity) AS TotalRevenue
FROM Orders o
-- 連接以獲取顧客和產品表
JOIN Customers c ON o.CustomerID = c.CustomerID
JOIN Products p ON o.ProductID = p.ProductID
WHERE YEAR(o.OrderDate) = 2024
GROUP BY c.CustomerName
HAVING SUM(p.Price * o.Quantity) > 1000;

通過使用CTE，我們可以將邏輯分離為更易讀的格式：

-- 定義CTE
WITH OrderDetails AS (
    SELECT o.OrderID, c.CustomerName, p.Price, o.Quantity, o.OrderDate
    FROM Orders o
    JOIN Customers c ON o.CustomerID = c.CustomerID
    JOIN Products p ON o.ProductID = p.ProductID
    WHERE YEAR(o.OrderDate) = 2024
)
--主查詢
SELECT CustomerName, SUM(Price * Quantity) AS TotalRevenue
FROM OrderDetails
GROUP BY CustomerName
HAVING SUM(Price * Quantity) > 1000;

代碼重用性

CTEs通過允許將相同的結果集在查詢的不同部分重複使用來幫助避免重複。如果多個計算或操作基於相同的數據集，您可以在CTE中定義一次並根據需要引用它。

假設我們需要計算電子商務數據庫中每個產品類別的平均銷售額和總銷售額。我們可以使用通用表達式（CTE）來定義這些計算一次，然後在後續查詢中重複使用。

-- 定義一個CTE來計算每個類別的總銷售額和平均銷售額
WITH CategorySales AS (
    SELECT Category, SUM(SalesAmount) AS TotalSales, AVG(SalesAmount) AS AverageSales
    FROM Products
    GROUP BY Category
)
-- 從CTE中選擇類別、總銷售額和平均銷售額
SELECT Category, TotalSales, AverageSales
FROM CategorySales
WHERE TotalSales > 5000;

其他應用

除了簡化查詢和代碼的重複使用外，CTE還有其他用途。我無法詳細介紹CTE的每種可能用途。如果您想繼續練習，我們的《SQL數據操作》課程是一個很好的選擇。不過，我將在這裡記錄一些主要的其他原因：

• 查詢組織和可讀性： CTE 通過將查詢劃分為邏輯、順序步驟來提高 SQL 代碼的可讀性。查詢過程中的每個步驟都可以用自己的 CTE 來表示，從而使整個查詢更容易理解。
• 階層式數據遍歷： CTE 可幫助導航階層關係，例如組織結構、父子關係或任何涉及嵌套級別的數據模型。遞歸 CTE 對於查詢階層數據很有用，因為它們允許您迭代地遍歷級別。
• 多層級聚合: CTE 可以幫助執行多個層級的聚合，例如在不同的粒度上計算銷售數據（例如按月份、季度和年份）。使用 CTE 來分離這些聚合步驟，確保每個層級都獨立和邏輯上計算。
• 從多個表中結合數據: 可以使用多個 CTE 來從不同的表中結合數據，使最終的組合步驟更有結構性。這種方法簡化了複雜的連接操作，確保來源數據在組織上邏輯清晰，以提高可讀性。

高級 SQL CTE 技術

CTE 支持高級 SQL 技術，使它們在不同的用例中具有靈活性和實用性。以下是一些 CTE 的高級應用。

在單個查詢中使用多個 CTE

在單個查詢中可以定義多個CTE，這允許進行複雜的轉換和計算。當問題需要多個數據處理階段時，每個CTE代表一個不同的階段時，這種方法很有用。

假設我們有一個名為Sales的表中的銷售數據，我們想要計算每個產品的總銷售額，識別總銷售額高於平均值的產品，並根據它們的總銷售額對這些產品進行排名。

WITH ProductSales AS (
    -- 步驟1: 計算每個產品的總銷售額
    SELECT ProductID, SUM(SalesAmount) AS TotalSales
    FROM Sales
    GROUP BY ProductID
),
AverageSales AS (
    -- 步驟2: 計算所有產品的平均總銷售額
    SELECT AVG(TotalSales) AS AverageTotalSales
    FROM ProductSales
),
HighSalesProducts AS (
    -- 步驟3: 過濾總銷售額高於平均值的產品
    SELECT ProductID, TotalSales
    FROM ProductSales
    WHERE TotalSales > (SELECT AverageTotalSales FROM AverageSales)
)
-- 步驟4: 為高銷售量的產品排名
SELECT ProductID, TotalSales, RANK() OVER (ORDER BY TotalSales DESC) AS SalesRank
FROM HighSalesProducts;

在上面的例子中；

• 第一個CTE（ProductSales）計算每個產品的總銷售額。

• 第二個 CTE（AverageSales）計算所有產品的平均總銷售額。

• 第三個 CTE（HighSalesProducts）過濾出銷售額超過平均值的產品。

• 最終查詢根據產品的總銷量對這些產品進行排名。

在UPDATE、DELETE和MERGE語句中使用CTE

當結合到UPDATE、DELETE和MERGE操作中時，CTE可以簡化數據操作任務，特別是在處理複雜篩選器或分層數據時。

使用CTE與UPDATE語句

假設我們有一個包含EmployeeSalary列的Employees表。我們希望對所有已在公司工作超過5年的員工加薪10%。

-- 定義一個CTE來查找5年前入職的員工
WITH LongTermEmployees AS (
    SELECT EmployeeID
    FROM Employees
    WHERE DATEDIFF(YEAR, HireDate, GETDATE()) > 5
)
-- 通過CTE識別的長期員工增加10%的薪水
UPDATE Employees
SET EmployeeSalary = EmployeeSalary * 1.1
WHERE EmployeeID IN (SELECT EmployeeID FROM LongTermEmployees);

CTE LongTermEmployees識別了工作超過五年的員工。UPDATE語句使用這個CTE有選擇性地增加薪水。

使用CTE與DELETE語句

現在假設我們有一個名為Products的表，並且想要刪除在過去兩年內沒有銷售的所有產品。我們可以使用CTE來篩選產品:

-- 定義一個CTE來識別在過去兩年內未銷售的產品
WITH OldProducts AS (
    SELECT ProductID
    FROM Products
    -- 使用DATEADD查找最後銷售日期超過兩年的產品
    WHERE LastSoldDate < DATEADD(YEAR, -2, GETDATE())
)
-- 從主表中刪除被識別為舊的產品
DELETE FROM Products
WHERE ProductID IN (SELECT ProductID FROM OldProducts);

CTE OldProducts識別出在過去兩年內未銷售的產品，然後DELETE語句使用這個CTE來移除這些產品。

使用CTE與MERGE語句

在SQL中，MERGE語句允許根據來源表中的數據對目標表進行條件更新、插入或刪除。在以下示例中，CTE MergedInventory結合了新的和現有的庫存數據。然後MERGE語句基於CTE數據更新現有產品的數量或插入新產品。

 - CTE 合併新舊庫存資料
WITH MergedInventory AS (
    SELECT ni.ProductID, ni.Quantity AS NewQuantity, i.Quantity AS CurrentQuantity
    FROM NewInventoryData ni
     - 使用 LEFT JOIN 包含所有新資料，即使不在目前庫存中
    LEFT JOIN Inventory i ON ni.ProductID = i.ProductID
)
 - 將準備好的資料合併到庫存表中
MERGE INTO Inventory AS i
USING MergedInventory AS mi
ON i.ProductID = mi.ProductID
 - 更新現有產品的新數量
WHEN MATCHED THEN
    UPDATE SET i.Quantity = mi.NewQuantity
 - 如果庫存中不存在，則插入新產品
WHEN NOT MATCHED BY TARGET THEN
    INSERT (ProductID, Quantity) VALUES (mi.ProductID, mi.NewQuantity);

遞迴通用表達式（CTEs）

遞迴 CTE 有助於執行高級和重複操作。

遞迴 CTE 簡介

遞迴 CTE 是一種特殊類型的 CTE，它在定義中引用自身，允許查詢執行重複操作。這使它們非常適合處理分層或樹狀結構數據，例如組織結構圖、目錄結構或產品組件。遞迴 CTE 逐步處理數據，一步一步返回結果，直到滿足終止條件。

錨定成員和遞迴成員

遞迴 CTE 包括兩個主要部分：

• 錨定成員： 定義啟動遞迴的基本查詢部分。
• 遞迴成員: 指引用 CTE 本身的部分，使其能夠執行”遞迴”操作。

假設我們有一個員工表，每行包含一個員工編號、員工姓名和經理編號。如果我們想要找到特定經理的所有直接和間接下屬，我們從錨定成員開始，確定頂級經理。錨定成員從員工編號 = 1的員工開始。

遞迴成員找到其經理編號與上一次迭代中的員工編號相匹配的員工。每個迭代檢索階層的下一級。

WITH EmployeeHierarchy AS (
    -- 錨定成員：選擇頂級經理
    SELECT EmployeeID, EmployeeName, ManagerID, 1 AS Level
    FROM Employees
    WHERE EmployeeID = 1  -- 從頂級經理開始
    UNION ALL
    -- 遞迴成員：找到向當前經理報告的員工
    SELECT e.EmployeeID, e.EmployeeName, e.ManagerID, eh.Level + 1
    FROM Employees e
    INNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT EmployeeID, EmployeeName, Level
FROM EmployeeHierarchy;

在SQL中CTE的潛在問題或限制

了解CTE的特性和限制對於編寫邏輯和可讀性的查詢很重要。讓我們來看看在不同數據庫中使用CTE時可能遇到的一些限制和潛在問題。

SQL Server和Azure的限制

在使用SQL Server或Azure Synapse Analytics時，SQL CTE存在一些特定於環境的限制。這些包括：

• SQL Server: 遞歸CTE的默認最大遞歸層級為100，可以使用OPTION (MAXRECURSION)提示進行修改。如果超出此限制而沒有進行調整，將會產生錯誤。CTE不能直接嵌套在彼此內部，也不能在另一個CTE內部定義。

• Azure Synapse Analytics: CTE對於某些SQL操作的支持有限，例如INSERT、UPDATE、DELETE和MERGE。此外，在Azure Synapse Analytics基於雲的環境中不支持遞迴CTE，這限制了執行某些階層數據操作的能力。

如果您發現自己在使用SQL Server，請知道DataCamp有許多優秀的資源可供參考。作為起點，我建議參加DataCamp的Introduction to SQL Server課程，以掌握SQL Server用於數據分析的基礎知識。您可以嘗試我們的SQL Server Developer職業途徑，該途徑涵蓋從交易和錯誤處理到時間序列分析的所有內容。我們的Hierarchical and Recursive Queries in SQL Server課程直接談到如何在SQL Server中撰寫高級查詢，包括涉及CTE的方法。

其他潛在問題

儘管 CTE 對於簡化複雜查詢非常有用，但你應該注意一些常見的陷阱。 它們包括以下內容：

• 遞迴 CTE 中的無限循環： 如果遞迴 CTE 的終止條件未滿足，可能會導致無限循環，使查詢無限運行。 為避免遞迴 CTE 無限運行，請使用 OPTION (MAXRECURSION N)提示來限制遞迴迭代的最大次數，其中 N 是指定的限制。

• 性能考量：如果遞迴深度很高或處理大型資料集時，遞迴CTE可能會變得耗費資源。為了優化性能，限制每次迭代處理的數據量，並確保適當的篩選以避免過多的遞迴層級。

何時使用CTE而非其他技術

雖然CTE適用於簡化涉及重複任務、衍生表、視圖和臨時表的查詢，但衍生表、視圖和臨時表也具有類似的目的。以下表格突顯了每種方法的優缺點以及何時使用每種方法。

結論

精通CTE需要練習，就像任何事情一樣：我建議嘗試DataCamp的SQL 資料分析師課程，以成為熟練的資料分析師。SQL報告課程也將幫助您在建立複雜報告和儀表板方面變得熟練，有效呈現數據。最後，您應該獲得SQL 資深認證，以展示您在使用SQL解決業務問題方面的精通能力，脫穎而出於其他專業人士之上。