CTE 通过 WITH 定义公共查询逻辑并复用，避免重复子查询，提升可读性与优化器分析效率；但默认不缓存结果，多次引用可能重复执行，重计算应留在下游以节省资源。
CTE 怎么替代重复子查询
直接用 WITH 把公共逻辑抽出来，定义一次，后面所有地方都引用名字。它不是复制粘贴，也不是物理表，只是给一段查询起个名，让 SQL 从“层层套娃”变成“分步流水线”。
比如你有两段逻辑都要用到 SELECT user_id, city FROM user_info WHERE dt='20231001' AND is_active=1，原来得写两遍；现在只需：
WITH active_users AS ( SELECT user_id, city FROM user_info WHERE dt = '20231001' AND is_active = 1 ) SELECT a.user_id, b.order_amount FROM active_users a JOIN order_detail b ON a.user_id = b.user_id WHERE b.status = 'paid' <p>UNION ALL</p><p>SELECT a.user_id, c.click_count FROM active_users a LEFT JOIN user_behavior c ON a.user_id = c.user_id AND c.page = 'home';
- 所有数据库（MySQL 8.0+、PostgreSQL、SQL Server、MaxCompute 等）都支持这种非递归 CTE 写法
-
active_users在整个主查询中可被多次引用，但不意味着结果被缓存——每次引用仍可能重新执行（除非引擎优化，如 PostgreSQL 12+ 显式加MATERIALIZED） - 别在 CTE 里塞太重的计算（比如上百万行聚合再 JOIN 大表），否则容易触发低效执行计划
为什么不能把 CTE 当成“缓存变量”用
很多人以为写了 WITH t AS (SELECT ...)，后面 SELECT * FROM t 和 JOIN t 就只算一遍。事实不是这样：大多数数据库默认对每个引用都重算——尤其当 CTE 被多次 JOIN 或出现在 UNION 分支里时。
典型误用：
WITH big_agg AS (
SELECT user_id, COUNT(*) cnt FROM events GROUP BY user_id
)
SELECT (SELECT COUNT(*) FROM big_agg) total,
(SELECT AVG(cnt) FROM big_agg) avg_cnt,
u.*
FROM users u
JOIN big_agg b ON u.id = b.user_id;
- 上面这段会至少执行三次
big_agg的全量聚合，不是一次算完复用 - 如果真需要物化中间结果，MySQL 不支持，PostgreSQL 需加
MATERIALIZED提示，MaxCompute 则靠优化器自动判断，不能强求 - 真正省资源的方式是：让 CTE 尽量轻（过滤早、投影少），把重计算留在下游 JOIN 或 WHERE 之后
CTE 和子查询在执行计划上到底差在哪
差别不在“一定更快”，而在“更容易让优化器看清意图”。嵌套子查询容易导致优化器放弃某些重写策略（比如谓词下推、连接顺序调整），而 CTE 的扁平结构更利于分析。
- 子查询嵌套过深（尤其是相关子查询）时，
EXPLAIN常显示DEPENDENT SUBQUERY，意味着外层每行都触发一次内层扫描 - CTE 本身不改变执行逻辑，但它把“先做什么、再做什么”的语义显式暴露给优化器，部分引擎（如 MaxCompute）会据此合并扫描、复用 Shuffle 数据
- 实测中，同样逻辑下 CTE 版本在 MaxCompute 上常比等价子查询快 15%~30%，主要省在减少重复表扫描和分区裁剪次数
哪些场景下 CTE 反而会让 SQL 更难调
不是所有嵌套都适合用 CTE 拆。盲目拆分会掩盖真实瓶颈，甚至引入新问题。
- 把单层简单过滤硬拆成 CTE（比如
WITH x AS (SELECT * FROM t WHERE id > 100)），纯属增加语法噪音，无实际收益 - 递归 CTE 如果没写好终止条件（比如漏掉
WHERE level ），可能无限循环或爆内存 - CTE 名称取太泛（如
t1,tmp）或太长（filtered_active_users_with_city_and_reg_date_after_2023），都会降低可读性——命名要像变量名一样精准、简短、带业务含义 - 调试时想单独看某个中间结果？别删 CTE 改主查询，直接把主查询替换成
SELECT * FROM cte_name即可——这是 CTE 最被低估的调试价值
CTE 的核心价值从来不是“性能魔法”，而是把隐式依赖变成显式步骤。一旦逻辑分层清晰，哪里慢、哪里错、哪里该加索引，就不再靠猜。
