在 SQL 中处理复杂查询时，CTE（公用表表达式）和临时表是两种常见的中间结果组织方式。很多人会把二者混为一谈，但实际上它们在生命周期、底层存储、执行计划以及可更新性上存在显著区别。本文从概念原理、执行行为、性能影响和实际场景几个角度展开，帮助你在不同业务查询中做出更合适的选择。

一、基础概念与作用域差异

CTE 全称 Common Table Expression，中文通常叫公用表表达式。它的本质是一个在单个 SQL 语句范围内临时存在的命名结果集，定义时并不产生实际数据副本，而是把内部的 SELECT 逻辑记录下来，等到外层查询引用它时展开。从作用域上看，CTE 只能在其定义之后紧跟的一条 SELECT、INSERT、UPDATE、DELETE 或 MERGE 语句中使用。语句执行结束，这个名称就不再存在。如果需要多次使用同一个中间结果，就不能只定义一个 CTE 然后反复查询它。

临时表则完全不同。以 SQL Server 为例，本地临时表使用 # 前缀，例如 #OrderSummary，它在 tempdb 中真实创建，数据会写入磁盘或内存。临时表在创建它的连接断开或显式执行 DROP TABLE 之前一直存在，因此可以在同一连接里的多个批处理、多条语句中反复引用。全局临时表使用 ## 前缀，其他会话也能访问，但并发和命名冲突问题更复杂。临时表具备普通表的很多能力，包括主键、索引、约束以及增删改操作。

-- CTE 仅在后续一条语句中有效
WITH OrderSummary AS (
    SELECT CustomerID, SUM(TotalAmount) AS TotalSpent
    FROM Orders
    WHERE OrderDate >= '2024-01-01'
    GROUP BY CustomerID
)
SELECT c.CustomerName, os.TotalSpent
FROM Customers c
JOIN OrderSummary os ON c.CustomerID = os.CustomerID;

-- 临时表可以在同一个会话里多次引用
CREATE TABLE #OrderSummary (
    CustomerID INT PRIMARY KEY,
    TotalSpent DECIMAL(18,2)
);
INSERT INTO #OrderSummary (CustomerID, TotalSpent)
SELECT CustomerID, SUM(TotalAmount)
FROM Orders
WHERE OrderDate >= '2024-01-01'
GROUP BY CustomerID;
SELECT c.CustomerName, os.TotalSpent
FROM Customers c
JOIN #OrderSummary os ON c.CustomerID = os.CustomerID;
-- 还可以继续使用
SELECT TOP 10 * FROM #OrderSummary ORDER BY TotalSpent DESC;
DROP TABLE #OrderSummary;

从语法上对比，CTE 的优势在于不落盘、书写简单，适合把复杂查询拆成多个可读的步骤；临时表的优势在于结果可以复用，且能通过索引和统计信息进一步优化。一个容易忽略的细节是：CTE 中如果有多次引用，优化器通常会将其内联展开，每一次引用都可能重新扫描底层表；临时表则在插入阶段完成一次计算，后续查询直接读取已经生成的副本。这个差异在多表连接、大量聚合或需要反复过滤的场景会直接影响执行时间。

二、执行计划与性能差异

性能差异是两者选择时最核心的考量。CTE 在绝大多数数据库产品中默认不会被物化。也就是说，它更像一个宏或者内联视图，本身不存储数据，也没有独立的统计信息。当优化器看到外层查询中的 CTE 引用时，会把 CTE 的定义直接合并到主查询里，然后基于底层基表的统计信息生成执行计划。如果同一个 CTE 被多次引用，优化器可能不会缓存第一次的结果，而是为每次引用分别生成访问路径。这种策略在数据量较小、只引用一次时通常没有负面影响，甚至因为省去了写 tempdb 的开销而更快。

临时表的行为正好相反。它在 CREATE TABLE 和 INSERT 阶段将结果集实际落到 tempdb，因此后续查询是读取已经生成的数据，不需要重复扫描原始大表。临时表还能拥有统计信息，优化器可以根据行数、数据分布做出更准确的选择。对于需要多次关联、多次聚合或结果集会被反复过滤的情况，临时表通常能提供更稳定的性能。不过写入 tempdb 也有代价，如果中间结果只有几百行且只用一次，创建临时表的 I/O 和元数据开销可能反而高于直接使用 CTE。

还有一个重要区别是索引支持。临时表可以创建聚集索引和非聚集索引，也可以在创建表时直接定义主键和唯一约束，而 CTE 没有实体结构，无法在其上创建索引。遇到中间结果需要被某个字段频繁过滤或排序的场景，临时表的索引往往能显著减少扫描范围。与之相对，CTE 的排序和过滤只能依靠底层基表索引或运行时排序，如果底层查询复杂，优化空间可能有限。

三、更新与事务语义对比

可更新性是另一个需要区分的维度。SQL Server 和 PostgreSQL 等数据库允许对 CTE 执行 UPDATE 或 DELETE，但本质上是修改 CTE 所引用的基表，而不是修改 CTE 这个名称。SQL Server 还要求通过 CTE 更新时，CTE 必须引用单个基表，且不能包含 DISTINCT、GROUP BY、HAVING、UNION 等导致结果不可映射到唯一行的结构。临时表则没有这些限制，它本身就是一张真实表，可以像操作普通表一样随意更新、删除、插入，还可以配合事务控制回滚。

从事务和锁角度看，CTE 只是查询定义，不产生独立的数据存储，所以不存在针对 CTE 本身的锁，但底层基表仍会按照常规隔离级别加锁。临时表的数据在事务中会受到事务控制，插入或更新后未提交的情况下，其他会话默认看不到这些变更。对于本地临时表，数据只对当前连接可见；事务回滚时，临时表中的数据变更也会被回滚。这意味着如果你需要在一个复杂流程中先计算中间结果、再根据中间结果做进一步修改，并且希望所有操作处于同一个事务边界内，临时表的语义通常更清晰。

-- 使用 CTE 删除重复行
WITH DuplicateOrders AS (
    SELECT OrderID,
           ROW_NUMBER() OVER (
               PARTITION BY CustomerID, OrderDate
               ORDER BY OrderID
           ) AS rn
    FROM Orders
)
DELETE FROM DuplicateOrders
WHERE rn > 1;

-- 临时表可继续修改和加索引
CREATE TABLE #FilteredOrders (
    OrderID INT,
    CustomerID INT,
    OrderDate DATE
);
INSERT INTO #FilteredOrders
SELECT OrderID, CustomerID, OrderDate
FROM Orders
WHERE OrderDate >= '2024-01-01';
CREATE INDEX IX_FilteredOrders_Customer ON #FilteredOrders(CustomerID);
UPDATE fo
SET fo.OrderDate = DATEADD(day, 1, fo.OrderDate)
FROM #FilteredOrders fo
WHERE fo.CustomerID = 1001;

从上面两段代码可以看出，CTE 在删除重复行时写法直观，但执行时最终还是作用在 Orders 基表上；临时表则先把数据独立出来，之后加索引和更新都只影响这张临时表，不会反向改动源表。如果业务逻辑要求中间结果与源数据彻底分离，临时表是更安全的选择。

四、递归查询与跨批处理场景

递归查询是 CTE 不可替代的典型场景。标准 SQL 中的递归 CTE 通过 UNION ALL 把锚点成员和递归成员连接起来，让查询能够沿着树形或图结构逐层展开。这类需求如果改用临时表，需要手动编写循环、游标或多次 INSERT，不仅代码复杂，还容易产生死循环或层级控制问题。大多数数据库优化器对递归 CTE 都有专门的执行策略和深度限制，例如 SQL Server 默认最大递归次数为 100，可以通过 OPTION (MAXRECURSION n) 调整。

跨批处理则是临时表更擅长的方向。CTE 的生命周期只限于定义它的那一条语句，一旦语句结束，名称立即失效。如果你需要在一个存储过程或脚本里先创建一个汇总结果，后续的多个查询、更新、二次分组都要使用这个结果，临时表能让数据在整个连接会话中保持可用。例如在 ETL 流程中，常常先把清洗后的明细数据写入临时表，再基于这张临时表做多次聚合、关联和导出。此时如果用 CTE，每个步骤都必须重新定义或重新扫描源表，维护性和性能都会下降。

-- 递归 CTE：遍历组织架构
WITH OrgTree AS (
    SELECT EmployeeID, ManagerID, EmployeeName, 0 AS Level
    FROM Employees
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.EmployeeID, e.ManagerID, e.EmployeeName, ot.Level + 1
    FROM Employees e
    INNER JOIN OrgTree ot ON e.ManagerID = ot.EmployeeID
)
SELECT EmployeeID, EmployeeName, ManagerID, Level
FROM OrgTree
ORDER BY Level, EmployeeID;

还有一种混合思路：先使用 CTE 组织复杂的中间逻辑，再通过 SELECT ... INTO #temp 将结果物化到临时表，这样既能保留 CTE 的可读性，又获得临时表的复用和索引能力。比如在报表查询中，先用递归 CTE 生成完整的层级路径，然后把结果插入临时表并建立索引，后续报表展示和筛选都可以基于临时表快速完成。不过需要注意的是，这种写法会让临时表的生成时点延后，如果后续只使用一次，仍然可能不如直接使用 CTE 简洁。