SQL 中的 CTE：完整指南与示例

如何创建 SQL CTE

创建 CTE 时，我们使用 WITH 关键字开始定义。CTE 的一般语法如下：

WITH cte_name (column1, column2, ...) AS (
    -- Query that defines the CTE
    SELECT ...
    FROM ...
    WHERE ...
)
-- Main query
SELECT ...
FROM cte_name;

其中：

• WITH： 启动 CTE 定义。
• cte_name： 分配给 CTE 的名称（用于后续引用）。
• 可选列列表： 指定 CTE 结果集的列名。
• 主查询： 通过名称引用 CTE，将其视作普通表。

来看一个示例。假设我们有一张 Employees 表，并想选择薪资高于 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 名称来引用它。

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

总结以上步骤：我们使用 WITH 关键字定义名为 HighEarningEmployees 的 CTE。内部查询用于生成临时数据集。主查询引用 HighEarningEmployees 来显示指定的 EmployeeID、FirstName 和 LastName 列。

为什么 SQL CTE 很有用

从上面的示例，您可能会疑惑：即使简单查询也能得到相同结果，为什么还要用 CTE？原因如下：

简化复杂查询

CTE 将复杂 SQL 语句拆分为更小、更易管理的部分，使代码更易读、易写、易维护。 

假设我们有三张表：Orders、Customers 和 Products。我们想要找出 2024 年每位客户产生的总营收。若不使用 CTE 来写查询，代码会显得繁杂，难以阅读和理解。

-- Standard SQL: Hard to read nested logic
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 EXTRACT(YEAR FROM o.OrderDate) = 2024
GROUP BY c.CustomerName
HAVING SUM(p.Price * o.Quantity) > 1000;

通过使用 CTE，我们可以将逻辑拆分为更可读的形式。先独立出“筛选与连接”的步骤，再进行聚合。

-- Standard SQL: Cleaner with 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 EXTRACT(YEAR FROM o.OrderDate) = 2024
)
-- Main query
SELECT 
    CustomerName, 
    SUM(Price * Quantity) AS TotalRevenue
FROM OrderDetails
GROUP BY CustomerName
HAVING SUM(Price * Quantity) > 1000;

代码可复用

CTE 通过允许复用同一结果集来避免重复。如果您需要先计算一个聚合（如求和），然后基于该聚合进行筛选，CTE 非常合适。

假设我们需要计算每个产品类别的平均和总销售额。我们在一个 CTE 中一次性定义这些计算：

WITH CategorySales AS (
    SELECT 
        Category, 
        SUM(SalesAmount) AS TotalSales, 
        AVG(SalesAmount) AS AverageSales
    FROM Products
    GROUP BY Category
)
-- Select from the CTE where the pre-calculated TotalSales is high
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 (
    -- Step 1: Calculate total sales for each product
    SELECT ProductID, SUM(SalesAmount) AS TotalSales
    FROM Sales
    GROUP BY ProductID
), 
AverageSales AS (
    -- Step 2: Calculate the average of those totals
    -- Note: We can reference the previous CTE (ProductSales) here
    SELECT AVG(TotalSales) AS AverageTotalSales
    FROM ProductSales
), 
HighSalesProducts AS (
    -- Step 3: Filter products above the average
    SELECT ps.ProductID, ps.TotalSales
    FROM ProductSales ps
    CROSS JOIN AverageSales av
    WHERE ps.TotalSales > av.AverageTotalSales
)
-- Step 4: Rank the results
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 语句

假设我们有一张 Employees 表，包含 EmployeeSalary 列。我们希望给在公司工作超过 5 年的所有员工加薪 10%。

-- Define a CTE to find employees hired more than 5 years ago
WITH LongTermEmployees AS (
    SELECT EmployeeID
    FROM Employees
    -- Standard SQL: Compare HireDate to 5 years before today
    WHERE HireDate <= CURRENT_DATE - INTERVAL '5' YEAR
)
-- Update salaries by 10% for long-term employees identified in the CTE
UPDATE Employees
SET EmployeeSalary = EmployeeSalary * 1.10
WHERE EmployeeID IN (SELECT EmployeeID FROM LongTermEmployees);

CTE LongTermEmployees 用于识别任职超过五年的员工。UPDATE 语句则借助该 CTE 有选择地提高薪资。

将 CTE 用于 DELETE 语句

现在，假设我们有一张名为 Products 的表，并希望删除过去两年未售出的所有产品。我们可以用 CTE 来筛选这些产品：

-- Define a CTE to identify products not sold in the last 2 years
WITH OldProducts AS (
    SELECT ProductID
    FROM Products
    -- Standard SQL: Filter for dates older than 2 years ago
    WHERE LastSoldDate < CURRENT_DATE - INTERVAL '2' YEAR
)
-- Delete products identified as old from the main table
DELETE FROM Products
WHERE ProductID IN (SELECT ProductID FROM OldProducts);

CTE OldProducts 识别出过去两年未售出的产品，随后 DELETE 语句据此删除这些产品。

将 CTE 用于 MERGE 语句

MERGE 语句允许基于来源表数据对目标表进行条件更新、插入或删除。在以下示例中，CTE MergedInventory 合并了新的与现有的库存数据。随后 MERGE 语句会根据 CTE 数据更新已存在产品的数量，或插入新产品。

-- CTE to prepare the source data for the merge
WITH MergedInventory AS (
    SELECT 
        ni.ProductID, 
        ni.Quantity AS NewQuantity
    FROM NewInventoryData ni
)
-- Merge the prepared data into the Inventory table
MERGE INTO Inventory AS target
USING MergedInventory AS source
    ON target.ProductID = source.ProductID
-- Update existing products with new quantities
WHEN MATCHED THEN
    UPDATE SET target.Quantity = source.NewQuantity
-- Insert new products if they don't exist in the inventory
WHEN NOT MATCHED THEN
    INSERT (ProductID, Quantity) 
    VALUES (source.ProductID, source.NewQuantity);

递归通用表表达式（CTE）

递归 CTE 是一种特殊的 CTE，它在定义中引用自身，从而使查询能够执行重复操作。这使其非常适合处理如组织架构图等层级数据。

递归 CTE 概述

递归 CTE 在其定义中引用自身，使查询能够执行重复操作。这使其非常适合处理层级或树状结构的数据，例如组织架构、目录结构或产品装配。递归 CTE 以迭代方式处理数据，逐步返回结果，直到递归成员不再返回新行（即终止条件）。

锚成员与递归成员

一个递归 CTE 由两部分组成：

• 锚成员： 定义开始递归的基础查询部分。
• 递归成员： 在查询中引用 CTE 自身的部分，使其能够执行“递归”操作。

假设我们有一张 Employees 表，每行包含 EmployeeID、EmployeeName 和 ManagerID。如果我们想查找某个经理的所有直接和间接下属，我们从识别最高层经理的锚成员开始。

注意：在标准 SQL（PostgreSQL、MySQL、SQLite）中，必须使用 RECURSIVE 关键字。

WITH RECURSIVE EmployeeHierarchy AS (
    -- Anchor member: select the top-level manager
    SELECT 
        EmployeeID, 
        EmployeeName, 
        ManagerID, 
        1 AS Level
    FROM Employees
    WHERE EmployeeID = 1  -- Starting with the top-level manager
    
    UNION ALL
    
    -- Recursive member: find employees who report to the current managers
    SELECT 
        e.EmployeeID, 
        e.EmployeeName, 
        e.ManagerID, 
        eh.Level + 1
    FROM Employees e
    INNER JOIN EmployeeHierarchy eh 
        ON e.ManagerID = eh.EmployeeID
)
-- Select the final result from the CTE
SELECT EmployeeID, EmployeeName, Level
FROM EmployeeHierarchy;

工作原理：

1. 锚： 查询首先运行锚成员，找到 ID 为 1 的员工。
2. 递归： 递归成员运行，查找其 ManagerID 与上一步得到的 EmployeeID 匹配的员工。
3. 循环： 此过程重复进行（第 1 级找到第 2 级，第 2 级找到第 3 级），直到不再找到新员工。

SQL 中 CTE 的潜在问题或限制

了解 CTE 的特性与限制，有助于编写逻辑清晰、可读性高的查询。我们来看看在不同数据库中使用 CTE 的一些限制和潜在问题。

SQL Server 与 Azure 的限制

在使用 SQL Server 或 Azure Synapse Analytics 时，SQL CTE 存在一些环境特定的限制，包括：

• SQL Server： 递归 CTE 的默认最大递归层级为 100。若超出此限制且未调整，会产生错误。CTE 定义不能直接嵌套在另一个 CTE 定义内部（但可以顺序串联多个 CTE）。
• Azure Synapse Analytics： 支持情况取决于具体的池类型。递归 CTE 目前在专用 SQL 池（原 SQL DW）中不受支持。但在无服务器 SQL 池中受支持。此外，某些 DML 操作（如在 CTE 中使用 UPDATE 或 DELETE）可能相较标准 SQL Server 有语法限制。

如果您需要使用 SQL Server，DataCamp 提供了大量优质资源可供参考。首先我建议学习 DataCamp 的SQL Server 入门课程，掌握用于数据分析的 SQL Server 基础。您也可以尝试我们的SQL Server 开发者职业路径，涵盖从事务与错误处理到时间序列分析的一切内容。我们的SQL Server 中的层级与递归查询课程则直击在 SQL Server 中编写高级查询的核心方法，包括涉及 CTE 的技巧。

其他潜在问题

尽管 CTE 有助于简化复杂查询，但也有一些常见陷阱需要注意，包括：

• 递归 CTE 的无限循环： 如果递归 CTE 的终止条件未满足，可能导致无限循环，使查询无限期运行。为避免递归无限执行，可使用 OPTION (MAXRECURSION N) 提示限制最大递归次数，其中 N 为指定上限。

  • 如何修复： 在 SQL Server 中，使用 OPTION (MAXRECURSION N) 提示限制最大递归次数。在 PostgreSQL 中，可使用 CYCLE 子句自动检测环路。

• 性能考量： 如果递归深度较大或处理的数据集很大，递归 CTE 可能会占用大量资源。为优化性能，应限制每次迭代处理的数据量，并进行恰当的过滤以避免过多的递归层级。

性能：CTE vs 子查询

一个常见的误区是认为 CTE 天生就比子查询更快。实际上，大多数现代查询优化器（如 SQL Server 与 PostgreSQL）会对标准 CTE 进行“内联”，也就是说它们与子查询的处理方式完全一致，性能并无差异。

不过，CTE 有时会通过物化带来性能提升，即数据库只计算一次 CTE 的结果，并在主查询的多次引用中进行缓存。

我建议主要因可读性而使用 CTE。性能收益具有情境性，取决于您的数据库如何处理缓存。

何时使用 CTE 与其他技术

虽然 CTE 适用于简化涉及重复任务的查询，但派生表、视图和临时表也有类似作用。下表概述了各方法的优缺点及其适用场景。

结论

掌握 CTE 和其他技能一样需要练习：我建议尝试 DataCamp 的SQL 助理数据分析师职业路径，成为一名熟练的数据分析师。SQL 报表课程也将帮助您熟练构建复杂报表与仪表板，以高效呈现数据。最后，您可以获得SQL 助理认证，展示您用 SQL 解决商业问题的能力，从众多专业人士中脱颖而出。