子查询不能直接多次引用，必须用CTE或临时表；CTE适用于单语句内复用且逻辑分层清晰的场景，临时表适合跨语句或需索引的场景。
子查询不能直接多次引用，必须用CTE或临时表包裹
SQL里写一个SELECT子查询后想在WHERE和SELECT里各用一次？不行。多数数据库（PostgreSQL、SQL Server、MySQL 8.0+）会报错或重复执行——既慢又不可靠。核心原因是：子查询不是命名对象，没法“取个名字再调用”。解决路径只有两条：用WITH定义CTE，或显式建TEMPORARY TABLE。
什么时候选CTE而不是临时表
CTE（Common Table Expression）适合单语句内复用、逻辑分层清晰的场景，比如先过滤再聚合再关联。它不落盘、无索引、只在当前查询生命周期存在。但要注意：
-
WITH定义的CTE不能在多个独立语句中复用（比如两个SELECT之间不能共享同一个CTE名） - PostgreSQL中CTE默认物化（即先算完再用），而MySQL 8.0+默认不物化——可能导致重复计算，加
MATERIALIZED提示可强制物化 - 如果CTE结果集大且后续要多次
JOIN，没索引会导致性能陡降，这时临时表更稳
示例（PostgreSQL）：
WITH user_orders AS ( SELECT user_id, COUNT(*) AS order_cnt FROM orders WHERE created_at >= '2024-01-01' GROUP BY user_id ) SELECT u.name, o.order_cnt FROM users u JOIN user_orders o ON u.id = o.user_id WHERE o.order_cnt > 5;
临时表更适合跨语句或需索引的场景
当你要在存储过程里反复查同一中间结果，或需要对子查询结果加索引加速JOIN，临时表是更务实的选择。注意不同数据库语法差异：
- PostgreSQL：
CREATE TEMP TABLE tmp_user_stats AS ...，自动在会话结束时清理 - SQL Server：
SELECT ... INTO #tmp_user_stats，#开头即会话级临时表 - MySQL：
CREATE TEMPORARY TABLE tmp_user_stats AS ...，仅当前连接可见 - 所有临时表都**不支持外键约束**，也**不能被其他会话访问**
别漏掉索引：建完临时表后立刻加索引，否则后续JOIN可能全表扫描：
CREATE INDEX idx_tmp_user_id ON tmp_user_stats(user_id);
嵌套子查询 vs CTE：性能陷阱常出在哪儿
看起来等价的写法，性能可能差十倍。典型坑点：
- 把本该一次计算的子查询写成相关子查询（correlated subquery），导致外层每行都重跑一遍——
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id)就比JOIN (SELECT DISTINCT user_id FROM orders) o ON u.id = o.user_id慢得多 - CTE里用了
ORDER BY或LIMIT却没配合OFFSET，某些引擎无法下推优化 - 在CTE里做复杂计算（如
JSON_EXTRACT、正则匹配），又在主查询里再次调用相同表达式——CTE不会自动缓存表达式结果，仍会重复计算
最稳妥的做法：先用EXPLAIN看执行计划，确认子查询是否被物化；如果发现重复扫描，就切到临时表并加索引。
