SQL CTE（WITH）解决了什么问题？

CTE通过命名临时结果提升复杂查询可读性与可维护性，支持非递归和递归两种形式，但生命周期仅限单条语句，不可跨查询引用或索引，性能表现因数据库而异，核心价值在于结构化表达而非优化。

CTE 让嵌套子查询可读性不再崩溃

当一个 SELECT 里套三层 (SELECT ...)，尤其每层还带 JOIN 和 GROUP BY，维护者第一反应是重写而不是调试。CTE 把这些“临时结果”提前命名、逐层定义，相当于给复杂查询做了变量拆解。不是语法糖，是结构化表达的刚需。

常见错误现象：把 CTE 当成视图或临时表来反复引用——它只在当前语句生命周期内有效，不能跨 SELECT 使用；也不支持在同一个 CTE 定义中多次引用自身（除非用 RECURSIVE）。

• 适用场景：报表类查询（如“先算各城市销售额，再取 Top 5，再关联城市信息”）
• 注意 WITH 必须紧贴最外层 SELECT，前面不能有注释或空行（某些数据库如 PostgreSQL 会报 ERROR: syntax error at or near "WITH"）
• 多个 CTE 用逗号分隔，顺序敏感：后一个可以引用前一个，但不能反过来

递归 CTE 解决树形/层级数据遍历难题

查组织架构、评论回复链、BOM 物料清单这类父子关系数据时，传统自连接写法极易出错且深度固定。递归 CTE 提供了声明式遍历能力，靠 UNION ALL 连接锚点（anchor）和递归成员（recursive member）两部分。

容易踩的坑：RECURSIVE 关键字在 PostgreSQL 中必须显式写出，在 SQL Server 中则隐含；MySQL 8.0+ 才支持，5.7 及之前直接报 ERROR 1235 (42000): This version of MySQL doesn't yet support 'CTE'。

• 锚点部分必须能独立执行并返回初始结果集（不能含递归引用）
• 递归成员中对 CTE 自身的引用必须在 FROM 子句右侧，且只能出现在 UNION ALL 的右半边
• 务必设置终止条件（比如 level ），否则可能无限循环导致超时或栈溢出

CTE 和子查询性能没必然优劣

很多人以为 “用了 WITH 就自动优化”，其实不然。PostgreSQL 会尝试将 CTE 视为优化器提示（MATERIALIZED 或 NOT MATERIALIZED），但 SQL Server 默认物化（materialize），而 MySQL 8.0 则倾向于内联展开（inline）——行为差异极大。

典型误判：看到执行计划里 CTE 对应节点耗时高，就认定“CTE 拖慢查询”。更可能是底层数据缺乏索引，或递归未剪枝，而非 CTE 本身的问题。

• 若 CTE 被多次引用且结果集小，物化可能提升性能；若只引用一次，内联反而减少中间结果开销
• PostgreSQL 12+ 可手动控制：WITH cte_name AS MATERIALIZED (...) SELECT ...
• 避免在 CTE 中写 SELECT *，尤其是后续只用其中几列——字段膨胀会拖慢物化过程

替代临时表但不等于临时表

CTE 常被当作轻量级临时表

使用（比如避免重复计算），但它不建对象、不占磁盘、不可索引，也不能被其他会话访问。真需要复用中间结果且数据量大，还是得上 CREATE TEMP TABLE。

一个隐蔽陷阱：在存储过程中用 CTE 处理大批量数据，可能因内存不足触发磁盘 spilling，而临时表可显式指定表空间或加索引缓解压力。

• CTE 生命周期 = 单条语句执行期；临时表生命周期 = 会话或事务级
• CTE 不支持 INSERT/UPDATE/DELETE（除递归 CTE 的锚点部分外）
• 调试时想看 CTE 中间结果？直接把 WITH ... SELECT 单独拿出来执行即可，这是它比子查询好调试的核心原因

CTE 的价值不在性能，而在让 SQL 表达意图变得可分解、可命名、可推演。真正难的从来不是写出来，而是让下一个人不用猜你当时在想什么。