WITH是MySQL 8.0+处理复杂查询的默认推荐路径，必须紧贴主查询前、支持多CTE逗号分隔与显式列名声明，递归需RECURSIVE关键字及终止逻辑，且可通过MATERIALIZE/NO_MERGE提示控制执行策略。
WITH 是目前最直接有效的手段，但不是加个 WITH 就自动可读——关键在怎么拆、怎么命、怎么连。
怎么用 WITH 把嵌套“摊平”成可读逻辑
多层嵌套难读，本质是把“先算什么”和“再连什么”混在同一行里。用 WITH 不是为了换行，而是显式声明计算顺序。
实操建议：
- 每个
WITH子句只做一件事：比如active_users_30d只过滤用户，不关联订单；shipped_orders只取状态为'shipped'的订单，不查用户信息 - 命名体现业务含义：
daily_sales_jan2024比tmp1强十倍；链式命名如user_orders → orders_with_ltv → high_value_cohorts能一眼看出依赖关系 -
SELECT必须显式列出字段，禁用SELECT *：减少中间数据量，也避免后续JOIN时字段冲突 - CTE 块之间用空行分隔，
AS后换行写SELECT，字段分行书写——这不是格式洁癖，是防止别名错位的底线
为什么 WITH 写对了但结果不对
最常翻车的不是语法错误，而是语义错位。数据库跑得通，但业务逻辑已经偏移。
典型现象：
-
WITH中没显式声明列名，导致后续SELECT *引用时字段顺序错乱，尤其跨 PostgreSQL 和 MySQL 迁移时 - 子句里用了
GROUP BY却漏了非聚合字段，MySQL 8.0+ 严格模式下直接报错ERROR 1055 (42000) -
JOIN多张表后没加表前缀，报错column 'id' is ambiguous -
CTE名和真实表名冲突，某些数据库（如旧版 SQLite）会优先解析为物理表而非 CTE
递归 WITH RECURSIVE 别硬套
看到“自关联”就写 WITH RECURSIVE，是中级 SQL 手最容易踩的坑。它不是高级语法勋章，而是专治树形结构的手术刀。
适用场景很窄：
- 真正有父子层级：组织架构、评论回复链、BOM 物料清单
- 路径需逐层展开：比如从 A 到 B 的跳转路径（但这类通常更适合图数据库）
替代方案往往更稳：
- 查上级部门名称？一条
JOIN dept d2 ON d1.parent_id = d2.id比递归清晰十倍 - 处理相邻行状态？用
LAG()/LEAD()窗口函数就够了 - 固定三级部门？三次
LEFT JOIN比递归更可控、易调试
滥用后果严重：PostgreSQL 默认递归深度限制为 100，超限直接报错 ERROR: stack depth limit exceeded；MySQL 需手动调 max_recursion_depth，且无默认保护。
什么时候该放弃 WITH，改用临时表
WITH 是逻辑分层工具，不是性能优化开关。它默认不物化，只是语法重写。
以下情况优先考虑 CREATE TEMP TABLE：
- 同一个 CTE 在主查询中被引用 ≥3 次，且中间结果行数 >1 万
- CTE 内含昂贵计算（如 JSON 解析、正则匹配、窗口函数 + 排序）
- 执行计划里反复出现同一段逻辑节点（用
EXPLAIN确认）
临时表能建索引、支持统计分析、可多次读取，但代价是 DDL 开销和 IO。别因为怕写 CREATE TEMP TABLE 就硬扛 CTE 的重复执行——复杂点永远在数据边界上，不在语法糖里。
