WITH子句通过提前命名并缓存中间结果简化嵌套子查询，是执行计划层面的优化；PostgreSQL倾向物化CTE，MySQL 8.0+默认不物化；递归CTE需锚点+递归成员、UNION ALL连接、避免聚合；复杂多层或需复用时应选临时表。
WITH 子句为什么能简化嵌套子查询
因为 WITH 把中间结果提前命名并缓存，避免同一段逻辑在 SELECT、WHERE、JOIN 里反复写。不是语法糖，是执行计划层面的优化机会——数据库（如 PostgreSQL、SQL Server）通常会物化 CTE 结果，尤其当被多次引用时。
但注意：MySQL 8.0+ 默认不物化 CTE，它可能重算多次；PostgreSQL 则倾向物化，除非加 MATERIALIZED / NOT MATERIALIZED 显式控制。
- 适合场景：
WITH后接多个 CTE（用逗号分隔），比如先算用户活跃度，再算地区分布，最后关联聚合 - 别在
WITH里写无过滤的全表扫描——CTE 不自动加索引，性能瓶颈照旧 - 别假设 CTE 一定比子查询快：简单单次引用时，内联子查询可能更轻量
递归 WITH（RECURSIVE）处理树形结构的实际写法
查组织架构、评论回复链、BOM 物料清单这类父子关系，WITH RECURSIVE 是唯一干净解法。核心是两部分：非递归锚点（SELECT 初始行） + 递归成员（JOIN 自身，靠 ON 关联父ID/子ID）。
容易漏的关键点：UNION ALL 必须写在锚点和递归之间，且递归部分不能出现聚合、GROUP BY、ORDER BY（除最外层）——否则直接报错 ERROR: recursive query "xxx" must not contain aggregates。
- 终止条件靠
WHERE控制递归深度，比如level < 5，否则可能死循环 - PostgreSQL 支持
SEARCH DEPTH FIRST或BREADTH FIRST控制遍历顺序，MySQL 不支持 - 字段名必须在锚点中定义好，递归部分要严格对齐列数和类型，否则报
column name list does not match
CTE 和临时表在复杂查询中的取舍
当 CTE 变得又长又多层，或者需要被多个后续语句复用（比如先查再更新），就该换临时表了。CTE 生命周期只限于单条语句，而 CREATE TEMP TABLE 能跨语句、可建索引、支持统计信息更新。
典型误用：把百万级中间结果塞进 CTE，还反复 JOIN —— 这时 CTE 可能被重算三次，而临时表加个 CREATE INDEX ON temp_table(user_id) 就能提速十倍。
- 用 CTE：逻辑清晰优先、单次使用、数据量小（
- 用临时表：需多次引用、要加索引、涉及 UPDATE/DELETE、数据量大或含复杂计算
- 注意：不同数据库临时表语法差异大——PostgreSQL 用
CREATE TEMP TABLE，SQL Server 用#temp，MySQL 用CREATE TEMPORARY TABLE
WITH 子句常见报错和绕过方式
ERROR: WITH clause containing a recursive query must be the first clause in the statement —— 这是最常卡住人的错误。意思是：只要用了 RECURSIVE，WITH 必须紧贴 SELECT 开头，前面不能有任何注释、空行、EXPLAIN 或其他前缀。
另一个高频问题：column "xxx" does not exist in WITH query，本质是列别名没在锚点里声明，或递归部分 SELECT 的列顺序/数量与锚点不一致。
- 别在 CTE 名后加括号写列名（如
WITH t(a,b) AS (...)）——某些数据库（如 older MySQL）不支持，老老实实写SELECT col1 AS a, col2 AS b - Oracle 用户注意：
WITH在 PL/SQL 块里不能直接用，得包在EXECUTE IMMEDIATE或视图里 - 想调试某一层 CTE？单独把它拎出来当主查询跑，比如
SELECT * FROM (your_cte_definition) t
WITH 真正难的不是语法，是判断哪一层该提成 CTE、哪一层该下推到 WHERE、哪一层其实该拆成临时表——这取决于数据量、重复引用次数、以及目标数据库对 CTE 的实际实现策略。别背规则，先 explain analyze 看执行计划。
