使用公用表表达式组织查询

12 分钟

使用复杂查询时，通常需要将逻辑分解为可管理的片段，或多次引用同一子查询。通用表表达式（CTE）提供了一种方法来定义仅在单个查询期间存在的临时命名结果集，使代码更易于读取和维护。

了解 CTE 语法

公共表表达式使用 WITH 子句定义，后跟 CTE 名称、可选列列表和定义结果集的查询。然后，可以在后续SELECT、INSERTUPDATE或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 与 Product 表联接，以与其销售指标一起显示产品名称。该 NULLIF 函数在计算平均单价时阻止除以零。

可以在单个 WITH 子句中定义多个公用表表达式 (CTE)，用逗号分隔它们。以后的 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语句。当修改逻辑需要复杂的筛选或计算时，此功能非常有用：

-- 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）和使用公用表表达式的递归查询。

反馈

此页面是否有帮助？