用常見的表格表達式組織查詢

12 分鐘

小提示

有關更多詳細信息，請參閱 文本和圖像 選項卡！

處理複雜查詢時，你常常需要將邏輯拆解成可管理的部分，或多次引用同一個子查詢。常用表格表達式（CTE）提供了一種定義僅在單一查詢中存在的臨時命名結果集的方法，使程式碼更易讀且易於維護。

了解 CTE 語法

共用資料表表達式是透過該 WITH 子句定義，接著是 CTE 名稱、可選的欄位清單，以及定義結果集的查詢。接著可以在接下來的 SELECT、 INSERT、 UPDATE或 DELETE 語句中引用 CTE。

WITH CTE_Name (Column1, Column2)
AS
(
    -- CTE query definition
    SELECT Column1, Column2
    FROM SomeTable
    WHERE SomeCondition = 'Value'
)
SELECT * FROM CTE_Name;

CTE 相較於導出資料表和子查詢有幾項優勢：

提升可讀性：將複雜查詢拆分為命名的邏輯區段後，會更容易理解
自我參照能力：遞迴 CTE 可自我參照，實現階層式資料遍歷
多重引用：在外部查詢中多次引用同一個 CTE，但不重新定義
模組化設計：透過定義多個 CTE 逐步建構複雜查詢

備註

CTE 是僅存在於查詢執行期間的暫時結果集。與傳統資料表不同，它們不會在使用它們的陳述式之外保留；也不需要明確清除。

建立非遞迴的 CTE

非遞迴 CTE 是以一個不引用自身的簡單查詢為基礎來定義結果集。此模式有助於簡化複雜連接、拆解多步驟計算，或改善程式碼組織。

以下範例使用CTE計算銷售指標，然後再加入產品資訊：

WITH SalesSummary AS
(
    SELECT 
        ProductID,
        SUM(OrderQty) AS TotalQuantity,
        SUM(LineTotal) AS TotalRevenue,
        COUNT(DISTINCT SalesOrderID) AS OrderCount
    FROM SalesLT.SalesOrderDetail
    GROUP BY ProductID
)
SELECT 
    p.Name AS ProductName,
    p.ProductNumber,
    p.ListPrice,
    ss.TotalQuantity,
    ss.TotalRevenue,
    ss.OrderCount,
    ss.TotalRevenue / NULLIF(ss.TotalQuantity, 0) AS AverageUnitPrice
FROM SalesLT.Product AS p
INNER JOIN SalesSummary AS ss
    ON p.ProductID = ss.ProductID
ORDER BY ss.TotalRevenue DESC;

此查詢首先建立一個名為 SalesSummary CTE 的 CTE，依產品彙整訂單細節，計算總銷售量、總營收及訂單數量。主要查詢會將此 CTE 與 Product 表格結合，顯示產品名稱及其銷售指標。該 NULLIF 函數在計算平均單價時避免除以零。

您可以在單一 WITH 子句中定義多個 CTE，並以逗號分隔。後續的 CTE 可參考較早的，促進漸進式資料轉換：

WITH CategorySales AS
(
    SELECT 
        p.ProductCategoryID,
        SUM(sod.LineTotal) AS CategoryRevenue
    FROM SalesLT.Product AS p
    INNER JOIN SalesLT.SalesOrderDetail AS sod
        ON p.ProductID = sod.ProductID
    GROUP BY p.ProductCategoryID
),
RankedCategories AS
(
    SELECT 
        ProductCategoryID,
        CategoryRevenue,
        RANK() OVER (ORDER BY CategoryRevenue DESC) AS RevenueRank
    FROM CategorySales
)
SELECT 
    pc.Name AS CategoryName,
    rc.CategoryRevenue,
    rc.RevenueRank
FROM RankedCategories AS rc
INNER JOIN SalesLT.ProductCategory AS pc
    ON rc.ProductCategoryID = pc.ProductCategoryID
WHERE rc.RevenueRank <= 5;

小提示

在建立包含多個 CTE 的查詢時，從最細緻的資料轉換開始，逐步彙整或過濾後續的 CTE。這種方法讓除錯變得更簡單，因為你可以獨立測試每個 CTE。

建立遞迴的 CTE

遞迴 CTE 會參考自身，以處理階層式或圖形結構的資料。遞迴 CTE 由兩部分組成：一個提供初始結果集的錨點成員，以及一個引用 CTE 以建立先前結果的遞迴成員。

遞迴 CTE 的一般語法如下：

WITH RecursiveCTE AS
(
    -- Anchor member: starting point
    SELECT columns
    FROM table
    WHERE starting_condition

    UNION ALL

    -- Recursive member: references the CTE
    SELECT columns
    FROM table
    INNER JOIN RecursiveCTE
        ON join_condition
)
SELECT * FROM RecursiveCTE;

一個常見的使用案例是穿越組織階層。考慮一個員工表格，每位員工都有一位經理：

WITH EmployeeHierarchy AS
(
    -- Anchor: Start with top-level managers (no manager)
    SELECT 
        EmployeeID,
        FirstName,
        LastName,
        ManagerID,
        0 AS Level,
        CAST(FirstName + ' ' + LastName AS NVARCHAR(500)) AS HierarchyPath
    FROM HumanResources.Employee
    WHERE ManagerID IS NULL

    UNION ALL

    -- Recursive: Find employees who report to previously found employees
    SELECT 
        e.EmployeeID,
        e.FirstName,
        e.LastName,
        e.ManagerID,
        eh.Level + 1,
        CAST(eh.HierarchyPath + ' > ' + e.FirstName + ' ' + e.LastName AS NVARCHAR(500))
    FROM HumanResources.Employee AS e
    INNER JOIN EmployeeHierarchy AS eh
        ON e.ManagerID = eh.EmployeeID
)
SELECT 
    EmployeeID,
    FirstName,
    LastName,
    Level,
    HierarchyPath
FROM EmployeeHierarchy
ORDER BY HierarchyPath;

這很重要

遞迴 CTE 若終止條件未被滿足，可能導致無限迴圈。 SQL Server 預設將遞迴限制在 100 層。用來 OPTION (MAXRECURSION n) 改變此限制，其中 n 是最大遞迴深度（無限為0）。

產生具有遞迴 CTE 的序列

遞迴 CTE 擅長產生數字序列或日期，且不需實體數字表。此技術用於建立日期範圍、填補資料空白或產生測試資料：

-- Generate a sequence of dates for the current month
WITH DateSequence AS
(
    -- Anchor: Get the first day of the current month
    SELECT CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AS DATE) AS DateValue

    UNION ALL

    -- Recursive: Add one day until we reach the end of the month
    SELECT DATEADD(DAY, 1, DateValue)
    FROM DateSequence
    WHERE DateValue < EOMONTH(GETDATE())
)
-- Output each date with its day name
SELECT DateValue, DATENAME(WEEKDAY, DateValue) AS DayName
FROM DateSequence
OPTION (MAXRECURSION 31);  -- Allow up to 31 iterations (max days in a month)

你可以將產生的序列與實際數據結合，以識別缺口或建立摘要報告：

-- Generate a numbers table from 1 to 1000
WITH Numbers AS
(
    -- Anchor: Start with 1
    SELECT 1 AS n
    UNION ALL
    -- Recursive: Increment until we reach 1000
    SELECT n + 1 FROM Numbers WHERE n < 1000
),
-- Convert numbers to dates for the entire year
DateRange AS
(
    SELECT DATEADD(DAY, n - 1, '2024-01-01') AS OrderDate
    FROM Numbers
    WHERE DATEADD(DAY, n - 1, '2024-01-01') <= '2024-12-31'
)
-- Count orders for each date, showing 0 for dates with no orders
SELECT 
    dr.OrderDate,
    COALESCE(COUNT(soh.SalesOrderID), 0) AS OrderCount
FROM DateRange AS dr
LEFT JOIN SalesLT.SalesOrderHeader AS soh
    ON CAST(soh.OrderDate AS DATE) = dr.OrderDate
GROUP BY dr.OrderDate
ORDER BY dr.OrderDate
OPTION (MAXRECURSION 366);  -- Allow up to 366 iterations (leap year)

搭配資料修改陳述式使用 CTE。

CTE 可與 INSERT、 UPDATE、 DELETE 和 SELECT 語句一起使用，而不只是 SELECT。當修改邏輯需要複雜濾波或計算時，此功能非常有用：

-- Update using a CTE to identify target rows
WITH DiscontinuedProducts AS
(
    SELECT ProductID
    FROM SalesLT.Product
    WHERE SellEndDate < DATEADD(YEAR, -2, GETDATE())
        AND ProductID NOT IN (
            SELECT DISTINCT ProductID 
            FROM SalesLT.SalesOrderDetail
            WHERE ModifiedDate > DATEADD(YEAR, -1, GETDATE())
        )
)
UPDATE SalesLT.Product
SET DiscontinuedDate = GETDATE()
WHERE ProductID IN (SELECT ProductID FROM DiscontinuedProducts);

此查詢使用CTE來識別應標記為停產的產品。 CTE 會尋找銷售結束日期超過兩年前，且在過去一年內，未出現在任何已修改訂單詳細資料中的產品。 UPDATE 這個陳述句接著為這些產品設定了 DiscontinuedDate 。將選擇邏輯分離成 CTE，查詢更容易獨立閱讀與測試。

欲了解更多關於常用表表達式的資訊，請參閱 WITH common_table_expression（Transact-SQL）及遞迴查詢使用常用表表達式。

意見反應

此頁面對您有幫助嗎？