CTE、子查询和临时表都是SQL中用于处理中间数据的常用方案，三者在性能表现和代码可读性上存在明显差异，需要结合具体场景选择合适的实现方式。
三种方案的基础定义
子查询
子查询是嵌套在主查询内部的查询语句，会先执行子查询得到结果集，再将结果集作为主查询的输入条件。子查询可以出现在SELECT、FROM、WHERE等子句中，根据返回结果的不同可以分为标量子查询、行子查询、列子查询和表子查询。
CTE（公用表表达式）
CTE通过WITH关键字定义临时命名的结果集，只在当前查询执行期间有效，作用域仅限于紧跟其后的那条查询语句。CTE可以把复杂的嵌套查询拆分成多个逻辑清晰的片段，提升代码的可阅读性，支持递归查询是它的重要特性之一。
临时表
临时表是存储在数据库临时空间中的物理表，分为局部临时表和全局临时表，生命周期可以跨多个查询会话。临时表需要显式创建、插入数据、使用数据，最后手动或自动清理，适合需要多次复用中间结果的场景。
可读性对比
从代码可读性来看，三者的表现差异明显：
- 子查询：当嵌套层数较少时可读性尚可，但如果出现多层嵌套，代码会显得非常臃肿，逻辑层级不清晰，后续维护时需要逐层梳理嵌套关系，理解成本较高。
- CTE：可读性最优，通过WITH关键字可以将复杂的查询逻辑拆分成多个有语义的命名片段，每个片段的逻辑独立，代码顺序和阅读顺序一致，逻辑层级一目了然。
- 临时表：可读性中等，需要显式编写建表、插数据、查数据的多段代码，虽然逻辑拆分清晰，但代码量相对较多，简单的场景使用会显得冗余。
性能对比
性能表现没有绝对的优劣，和数据库引擎、数据量、查询逻辑都有关系：
小数据量简单查询场景
三种方式的性能差异极小，数据库优化器通常能对简单的子查询和CTE做优化，执行计划基本一致，临时表因为涉及磁盘IO，反而可能稍慢。
大数据量复杂查询场景
- 子查询如果无法被优化器优化，可能会导致重复执行，性能下降。
- CTE在很多数据库中是逻辑上的临时结果，不会物化存储，每次引用都会重新执行定义的逻辑，如果多次引用同一个CTE，可能会导致重复计算。
- 临时表会将中间结果物化到磁盘，后续多次引用不需要重复计算，适合需要多次复用中间结果的场景，但建表和写数据的过程会有额外的IO开销。
递归查询场景
只有CTE支持递归查询，子查询和临时表都无法直接实现递归逻辑，这种场景下CTE是唯一选择。
适用场景总结
| 方案类型 | 适用场景 | 不适用场景 |
|---|---|---|
| 子查询 | 简单的一次性过滤、小数据量的简单逻辑、不需要复用的中间结果 | 多层嵌套逻辑、需要多次复用的中间结果、递归查询 |
| CTE | 复杂逻辑拆分、递归查询、需要提升代码可读性的场景 | 需要多次复用中间结果且数据量大的场景、数据库不支持CTE的场景 |
| 临时表 | 大数据量中间结果需要多次复用、跨多个查询会话使用中间结果 | 简单的一次性查询、小数据量的简单逻辑 |
代码示例对比
子查询示例
查询订单金额大于平均订单金额的用户信息：
SELECT
user_id,
user_name
FROM users
WHERE user_id IN (
SELECT user_id
FROM orders
GROUP BY user_id
HAVING SUM(order_amount) > (
SELECT AVG(total_amount)
FROM (
SELECT SUM(order_amount) AS total_amount
FROM orders
GROUP BY user_id
) AS user_order_total
)
);
CTE示例
实现同样的逻辑，使用CTE拆分后可读性明显提升：
WITH user_order_total AS (
SELECT
user_id,
SUM(order_amount) AS total_amount
FROM orders
GROUP BY user_id
),
avg_order_amount AS (
SELECT AVG(total_amount) AS avg_amount
FROM user_order_total
)
SELECT
u.user_id,
u.user_name
FROM users u
JOIN user_order_total uot ON u.user_id = uot.user_id
CROSS JOIN avg_order_amount aoa
WHERE uot.total_amount > aoa.avg_amount;
临时表示例
同样的逻辑使用临时表实现，适合需要多次复用中间结果的场景：
-- 创建临时表存储用户订单总金额
CREATE TEMPORARY TABLE tmp_user_order_total AS
SELECT
user_id,
SUM(order_amount) AS total_amount
FROM orders
GROUP BY user_id;
-- 创建临时表存储平均订单金额
CREATE TEMPORARY TABLE tmp_avg_order_amount AS
SELECT AVG(total_amount) AS avg_amount
FROM tmp_user_order_total;
-- 查询目标用户
SELECT
u.user_id,
u.user_name
FROM users u
JOIN tmp_user_order_total uot ON u.user_id = uot.user_id
JOIN tmp_avg_order_amount aoa ON 1=1
WHERE uot.total_amount > aoa.avg_amount;
-- 清理临时表
DROP TEMPORARY TABLE IF EXISTS tmp_user_order_total;
DROP TEMPORARY TABLE IF EXISTS tmp_avg_order_amount;
选择建议
实际开发中可以按照以下优先级选择：
- 如果是简单的一次性查询，优先选择子查询，代码简洁。
- 如果查询逻辑复杂，需要拆分逻辑提升可读性，或者需要递归查询，优先选择CTE。
- 如果中间结果需要多次复用，且数据量较大，选择临时表更合适。
- 最终选择可以结合执行计划分析，优先保证性能达标的前提下，选择可读性更好的方案。