CTE还是临时表？SQL中间结果选型与MySQL/PG版本差异避坑

写 SQL 的时候，我们是不是经常遇到这种需求：

"先找出活跃用户，再拿这批用户去查订单，再查商品，再算转化率，最后还要跟上个月对比……"

怎么用 SQL 实现？脑子里自然冒出三个选项：

• 子查询：一层套一层，像俄罗斯套娃，写到最后自己都晕。
• CTE（公用表表达式）：用 WITH xxx AS (...) 拆成几步，像搭积木，逻辑清晰。
• 临时表：先建个临时表存中间结果，后面想怎么用就怎么用。

先看一个简单例子：

-- 子查询写法（套娃式）
SELECT user_id, cnt, cnt * 100.0 / total AS pct
FROM (
    SELECT user_id, COUNT(*) as cnt
    FROM orders
    WHERE created_at >= '2025-01-01'
    GROUP BY user_id
) a
CROSS JOIN (
    SELECT COUNT(*) as total
    FROM orders
    WHERE created_at >= '2025-01-01'
) b;

-- CTE写法（分层式）
WITH active_users AS (
    SELECT user_id, COUNT(*) as cnt
    FROM orders
    WHERE created_at >= '2025-01-01'
    GROUP BY user_id
),
total_orders AS (
    SELECT COUNT(*) as total
    FROM orders
    WHERE created_at >= '2025-01-01'
)
SELECT a.user_id, a.cnt, a.cnt * 100.0 / t.total AS pct
FROM active_users a
CROSS JOIN total_orders t;

-- 临时表写法（稳如老狗）
CREATE TEMPORARY TABLE temp_active AS
SELECT user_id, COUNT(*) as cnt
FROM orders
WHERE created_at >= '2025-01-01'
GROUP BY user_id;

CREATE TEMPORARY TABLE temp_total AS
SELECT COUNT(*) as total
FROM orders
WHERE created_at >= '2025-01-01';

SELECT a.user_id, a.cnt, a.cnt * 100.0 / t.total AS pct
FROM temp_active a
CROSS JOIN temp_total t;

看起来 CTE 最清爽！但别被表象骗了。在某些数据库里，CTE 可能会偷偷"重复计算"，性能直接翻车；临时表看起来笨重，但在特定场景下反而是最优解。

一、多次复用同一结果集

这是最最最常踩的坑。假设需求是这样：找出"2025年Q1下单≥5次的用户"，然后用这个用户列表去关联订单表两次，分析他们买了哪些相同商品。如果复用 CTE 来实现，可别想当然地以为 CTE 只算一次——有的数据库会默默地算两次。下面分数据库细看。

1、PostgreSQL

v12 之前：CTE默认物化（Materialized），算一次，存起来，后面直接读。

v12+：默认不物化！除非手动加 MATERIALIZED。

WITH active_users AS MATERIALIZED (  -- ← 必须加MATERIALIZED！
    SELECT ...
)
SELECT ...
FROM active_users a
JOIN active_users b ...;

不加的后果？

• EXPLAIN 里会看到 Index Scan on orders 出现两次。
• 执行时间翻倍。
• 缓冲区命中翻倍。
• 老项目升级到 PG12+ 后性能暴跌，十有八九是这个锅。

避坑建议：在 PG v12+ 里，所有复用 CTE 必须加 MATERIALIZED，Code Review（代码审查）时要重点查。

2、MySQL

MySQL 8.0虽然支持CTE，但默认是内联展开的。也就是说，你写：

WITH cte AS (...)
SELECT * FROM cte a JOIN cte b ...;

MySQL 会把它展开成：

SELECT * FROM (...) a JOIN (...) b ...;

这样，orders 表被扫描两次。

怎么验证？

EXPLAIN FORMAT=JSON 查询语句

看输出里有没有：

"select_id": 2 和 "select_id": 3 → 两个独立子查询
"rows_examined_per_scan": 500000 × 2 → 扫描行数翻倍

怎么解决？

（1）换用临时表（最稳）：

CREATE TEMPORARY TABLE temp_active AS
SELECT ...;

SELECT a.*, b.*
FROM temp_active a
JOIN temp_active b ...;

再看执行计划，orders 只扫一次，成本骤降。

（2）用 MATERIALIZED CTE（MySQL 8.0.21+）：

WITH cte AS MATERIALIZED (...)
SELECT ...

此时看 EXPLAIN 里有没有 "using_temporary_table": true，有的话问题就解决了。

避坑建议：禁止 MySQL 在 JOIN/UNION 里用裸 CTE！必须用临时表或 MATERIALIZED！

3、SQL Server / Oracle：放心用 CTE

这两个数据库优化器比较聪明，看到 CTE 被复用会自动物化，用"表假脱机"（Table Spool）或"临时表转换"来缓存中间结果。

SQL Server 验证方法：

SET STATISTICS PROFILE ON;
-- 执行查询
SET STATISTICS PROFILE OFF;

看执行计划里有没有：

Table Spool (Lazy Spool) → 出现两次，但指向同一个物化结果
Index Seek → 只执行一次

Oracle 验证方法：

EXPLAIN PLAN FOR 查询语句;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

找：

TEMP TABLE TRANSFORMATION → 用了临时表
LOAD AS SELECT → 数据加载到临时表
TABLE ACCESS FULL | ORDERS → 只出现一次

避坑建议：SQL Server / Oracle 里可以放心用 CTE，但 Oracle 建议加 /*+ MATERIALIZE */ Hint 更保险。

4、SQLite：CTE 默认内联，临时表才是最优解

SQLite 默认会把 CTE 内联展开，导致重复计算：

EXPLAIN QUERY PLAN
WITH cte AS (...)
SELECT * FROM cte a JOIN cte b ...;

输出：

SCAN TABLE users AS cte
SCAN TABLE users AS cte → 扫了两次！

怎么解决？

（1）推荐临时表：

CREATE TEMP TABLE temp_high AS SELECT ...;
SELECT * FROM temp_high a JOIN temp_high b ...;

（2）MATERIALIZED（SQLite 3.35.0+）：

WITH cte AS MATERIALIZED (...)
SELECT ...

看输出是不是：

SCAN SUBQUERY 1 AS a
SCAN SUBQUERY 1 AS b → 同一个子查询编号，说明物化成功

避坑建议：SQLite 生产环境无脑用临时表，轻量场景才用 CTE。

二、单次引用：性能差不多，选 CTE 就对了

如果中间结果只用一次，CTE、子查询、临时表性能基本一样。但 CTE 可读性吊打子查询。

比如这个需求：计算销售员 2025 年 Q1 业绩，对比 2024 年 Q1，算增长率，再排名前 10。

用 CTE 写：

WITH current_qtr AS (...),
     prev_qtr AS (...),
     ranked_sales AS (...)
SELECT ... FROM ranked_sales WHERE rk <= 10;

新人一看就懂：先算今年，再算去年，再合并排名。

如果我们用子查询写：

SELECT ... FROM (
    SELECT ... FROM (
        SELECT ... FROM sales WHERE ...
    ) c LEFT JOIN (
        SELECT ... FROM sales WHERE ...
    ) p ...
) ranked_sales WHERE rk <= 10;

新人：这啥？眼睛花了。

EXPLAIN 验证：以上两者执行计划几乎一模一样，性能无差别。

建议：只要逻辑稍微复杂点，无脑选 CTE。Code Review 时会被夸"结构清晰"。

三、递归查询：CTE 是唯一解

有些场景，比如：

• 组织架构（找某个员工的所有上级）
• 数据血缘（列 A 是从列 B、列 C、列 D 一步步算来的）
• 商品分类树（一级分类→二级→三级）

这些层级、树形、链式结构，只有 CTE 能搞定：

WITH RECURSIVE org_path AS (
    SELECT ... WHERE manager_id IS NULL  -- 根节点
    UNION ALL
    SELECT ... JOIN org_path ...         -- 递归找下级
)
SELECT ...;

子查询？临时表？JOIN？统统做不到。

看执行计划时，主要关注：

• Recursive Union
• WorkTable Scan（递归工作表）
• depth < 10（防死循环）

建议：建立递归查询模板库，统一加深度限制，防止无限循环把数据库搞挂。

四、临时表：不是备胎，有时是唯一正确答案

到此你可能觉得"用临时表 = 水平低"，这是大错特错。临时表在以下场景是唯一正确答案：

场景 1：MySQL / SQLite 里复用中间结果

前面说过了，这两个数据库的 CTE 默认不物化，复用就翻车，临时表是最优解。

场景 2：中间结果特别大，要加索引优化后续查询

CTE 不能加索引，临时表可以！

CREATE TEMPORARY TABLE temp_active AS
SELECT user_id, COUNT(*) as cnt
FROM huge_orders
GROUP BY user_id;

CREATE INDEX idx_temp_active_user ON temp_active(user_id);

-- 后续JOIN飞快
SELECT ... FROM temp_active a JOIN users u ON a.user_id = u.id;

场景 3：中间结果要被多个查询复用

比如算完活跃用户，要分别给"订单分析""商品推荐""客服工单"三个模块用。CTE 作用域只限当前语句，临时表可以跨语句、跨过程复用。

CALL analyze_orders();
CALL recommend_products();
CALL generate_tickets();
-- 三个存储过程都能访问temp_active

建议：临时表不是"笨办法"，该用就用，别扭捏。

五、子查询：不是垃圾，是"轻量级刺客"

最后别把子查询一棍子打死。在以下场景，子查询反而更优：

• 超简单逻辑：SELECT * FROM users WHERE id IN (SELECT user_id FROM orders)——没必要上 CTE。
• 标量子查询：SELECT name, (SELECT COUNT(*) FROM orders WHERE user_id = u.id) as cnt FROM users u——优化器能很好处理。
• 数据库不支持 CTE：比如老版本 MySQL、某些嵌入式数据库。

建议：子查询是"轻骑兵"，适合小规模、单点突击，别让它干重活。

总结

场景	首选方案	原因
复用中间结果（MySQL/SQLite）	临时表	CTE 默认内联，重复计算
复用中间结果（PG12+）	CTE + MATERIALIZED	不加会重复计算
复用中间结果（SQL Server/Oracle）	CTE	优化器自动物化
单次引用、逻辑复杂	CTE	可读性碾压
递归查询	CTE（RECURSIVE）	唯一解
中间结果巨大、需要索引	临时表	CTE 不能加索引
跨语句复用	临时表	CTE 作用域仅限单语句
超简单单点查询	子查询	轻量直接

说到底，CTE、临时表和子查询没有绝对的好与坏，只有匹配场景才能选对工具。先看数据库版本，再看中间结果的复用方式，最后考虑可读性和可维护性，基本上就不会选错。