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 表达意图变得可分解、可命名、可推演。真正难的从来不是写出来，而是让下一个人不用猜你当时在想什么。