cte应优先用于重复出现且逻辑独立的子查询，而非单纯层数深；需避免物化开销、作用域误用及命名不规范等问题。
能直接替代，但必须分清哪些嵌套该抽、哪些不该动——硬套CTE反而让SQL更难懂、更慢。
哪些嵌套子查询适合用 WITH 拆出来
重点不是“层数深”，而是“重复出现”和“逻辑独立”。比如同一段子查询在 SELECT、WHERE、JOIN 里各用一次，或者被两个 UNION 分支各自写一遍，就该拎成 CTE。
- 同一聚合逻辑（如
COUNT(*)+GROUP BY user_id）在主查询中被引用 ≥2 次 - 子查询含
ROW_NUMBER()或RANK()等窗口函数，且外层要基于它再过滤 - 多步数据预处理：先过滤原始表 → 再关联维度 → 最后算指标，每步都可命名（如
filtered_events、enriched_users） - 避免把只出现一次、又带相关引用（correlated subquery）的子查询硬塞进 CTE，比如
WHERE x = (SELECT y FROM t WHERE t.id = outer.id)—— 这类改用EXISTS或JOIN更自然
WITH 在 PostgreSQL 中的物化行为影响性能
PostgreSQL 12+ 默认对 CTE 进行物化（materialize），相当于自动建临时表。这在多数场景下提升复用效率，但也有代价：
- 如果 CTE 结果集很大，但只被主查询引用一次，物化反而浪费 I/O 和内存；此时加
WITH NOT MATERIALIZED可绕过（如WITH NOT MATERIALIZED recent_orders AS (...)） - 物化后，优化器无法将外层
WHERE条件下推到 CTE 内部执行，可能错过索引——若 CTE 本身数据量大，记得检查EXPLAIN ANALYZE输出里是否有 “CTE Scan on xxx” 后紧跟全表扫描 - MySQL 8.0+ 的 CTE 不默认物化，只是语法重写，所以同样写法在两库上性能表现可能完全不同
常见错误：CTE 引用时机和作用域
CTE 不是变量，不能在定义前使用，也不能跨作用域共享：
-
ERROR: relation "t" does not exist：把 CTE 名写在FROM之后，却试图在WHERE子句里提前引用它（例如WHERE id IN (SELECT id FROM t)写在FROM t前） -
UNION各分支需各自定义所需 CTE，不能共用外部定义的 CTE；若想复用，得把 CTE 提到整个UNION外层 - CTE 中写
ORDER BY无意义（除非配合LIMIT），PostgreSQL 允许但不保证结果顺序；真要排序，放在最终SELECT里
递归 CTE 是唯一能替代自连接树形遍历的方案
当需要查组织架构、评论回复链、BOM 物料清单这类层级关系时，必须用 WITH RECURSIVE，其他方式（如多层 LEFT JOIN）要么写死深度，要么漏数据。
- 必须包含非递归项（anchor member）和递归项（recursive member），用
UNION ALL连接 - 递归项中
JOIN的方向必须明确指向父/子关系，否则会无限循环；建议加MAX_RECURSION_DEPTH参数（PostgreSQL 通过statement_timeout或自定义计数器模拟） - 不要在递归 CTE 的非递归部分用聚合或
GROUP BY，会导致无法启动递归流程
真正容易被忽略的点是：CTE 的命名和字段暴露。起名别用 t1、subq，而要用 active_users_30d 这类带时间范围和业务含义的名字；每个 CTE 显式 SELECT 字段，不写 * —— 否则后续 JOIN 时字段冲突、别名覆盖、类型隐式转换都会悄悄出问题。
