CTE是MySQL 8.0+处理复杂查询的默认推荐路径，非语法糖；必须以WITH开头紧贴主查询前，支持多CTE逗号分隔、列名显式声明、递归需RECURSIVE关键字及终止逻辑，并可通过MATERIALIZE/NO_MERGE提示控制执行策略。
CTE 不是语法糖，它是 MySQL 8.0+ 处理复杂查询的默认推荐路径——只要你的 MySQL 版本 ≥ 8.0.0，就该优先用 WITH 替代多层子查询。
CTE 基本写法：WITH 必须紧贴主查询前，不能丢在中间
很多人写完 SELECT 才想起来加 WITH，结果报错 ERROR 1064 (42000): You have an error in your SQL syntax。这是因为 WITH 是整个语句的前置修饰子句，不是独立语句。
-
WITH必须出现在SELECT、UPDATE或DELETE的最前面（可跟在注释后，但不能有其他语句） - 多个 CTE 用逗号分隔，后一个可以引用前一个（顺序敏感）
- 列名可显式声明：
WITH cte (id, name) AS (SELECT user_id, username FROM users)，避免隐式类型推断出错 - 不声明列名时，列名来自第一个
SELECT的别名或原始字段名；若含UNION ALL，以第一个分支为准
嵌套子查询转 CTE：先拆逻辑，再拼主查
典型错误是把原嵌套 SQL 整段搬进 AS () 里，却没重写关联条件——导致结果错乱或性能更差。
- 识别重复子查询：比如同一张表被
JOIN两次、或WHERE里多次出现相同聚合子查询 - 把子查询单独拎出，起个语义名（如
dept_avg、top_users），确保它能独立执行并返回合理结果集 - 主查询中用
JOIN或WHERE ... IN (SELECT ...)方式引用，别漏掉ON条件或关联字段类型一致（比如INT对BIGINT可能触发隐式转换） - 示例：原写法
WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.dept_id = e1.dept_id)→ 提取为dept_avg AS (SELECT dept_id, AVG(salary) AS avg_salary FROM employees GROUP BY dept_id)，主查改用JOIN dept_avg ON e1.dept_id = dept_avg.dept_id WHERE e1.salary > dept_avg.avg_salary
递归 CTE：必须带 RECURSIVE 关键字，且 anchor 和 recursive 部分要能收敛
忘记加 RECURSIVE 会直接报错 ERROR 1223 (HY000): Can't execute the query because it contains recursive WITH；而没设终止条件则可能无限循环或超内存。
- 语法强制要求：
WITH RECURSIVE tree AS ( ... )，缺RECURSIVE关键字不认作递归 CTE - anchor 成员（初始结果）和 recursive 成员（自引用部分）必须字段数、类型一致，可用
CAST显式对齐 - 必须有明确终止逻辑：常见做法是加层级计数（
level ）或父 ID 不等于子 ID（防自环） - MySQL 默认递归深度上限是 1000，可通过
SET cte_max_recursion_depth = 2000临时调高（仅 session 级）
性能与调试：别默认以为 CTE 一定更快，MATERIALIZE 和 NO_MERGE 是关键开关
CTE 默认行为由优化器决定：可能“展开”（merge）进主查询，也可能物化（materialize）成临时结果。这直接影响执行计划和速度。
- 想强制物化（适合小结果集、多次引用）：在
AS前加提示/*+ MATERIALIZE */，例如WITH /*+ MATERIALIZE */ top_users AS (SELECT ...) - 想阻止合并（适合控制驱动表顺序、隔离逻辑）：用
/*+ NO_MERGE */，但注意它和MATERIALIZE互斥 - CTE 结果超
tmp_table_size会自动落磁盘，此时要检查max_heap_table_size和tmp_table_size配置是否合理 - 调试技巧：把 CTE 单独拿出来
SELECT * FROM cte_name运行，验证数据和性能，比在嵌套里猜快得多
CTE 最容易被忽略的点是生命周期和作用域：它只在当前语句内有效，不能跨语句复用；多个 CTE 同名会报错；子查询内部的 WITH 是独立作用域，外层 CTE 不可见。写的时候盯着报错信息里的“unknown table”或“not found”关键词，基本就是名字或作用域错了。
