MySQL 8.0+中CTE是默认推荐路径而非语法糖，必须以WITH开头紧贴主查询前，支持多CTE逗号分隔、显式列名声明、递归需RECURSIVE关键字及终止条件，且不可嵌套于子查询或标量子查询中。
WITH 不是配置项，是语法结构——MySQL 8.0+ 只要版本达标（≥ 8.0.0），无需额外配置，直接用 WITH 就行。但写错位置、漏关键字、混用子查询，照样报错。
CTE 必须紧贴主查询最前面，不能嵌套
常见错误是把 WITH 写在 SELECT 后面，或塞进子查询里：
-
SELECT * FROM (WITH t AS (...) SELECT ...) s;→ 报错ERROR 1064 -
SELECT id, (WITH t AS (...) SELECT COUNT(*) FROM t) cnt FROM users;→ 不支持标量子查询内用WITH
正确姿势：整个语句以 WITH 开头，后接主查询（SELECT/UPDATE/DELETE）。
示例：
WITH recent_orders AS ( SELECT order_id, customer_id, amount FROM orders WHERE order_date >= '2025-01-01' ) SELECT customer_id, SUM(amount) AS total FROM recent_orders GROUP BY customer_id;
多 CTE 要用逗号分隔，引用顺序敏感
多个 CTE 写在一个 WITH 子句里，用逗号隔开，后一个可引用前一个，但不能反向依赖。
-
WITH a AS (...), b AS (SELECT * FROM a), c AS (SELECT * FROM b)✅ -
WITH a AS (SELECT * FROM b), b AS (...)❌ 报错Unknown table 'b'
列名建议显式声明，尤其含表达式时：
WITH sales_summary (customer_id, total, avg_order) AS ( SELECT customer_id, SUM(amount), AVG(amount) FROM orders GROUP BY customer_id ) SELECT * FROM sales_summary WHERE total > 1000;
递归 CTE 必须带 RECURSIVE 关键字
哪怕逻辑再简单，漏写 RECURSIVE 就直接失败：
-
WITH tree AS (... UNION ALL ...)→ERROR 1223 -
WITH RECURSIVE tree AS (...)✅
递归部分只能出现在 FROM 或 JOIN 中，不能在 WHERE 子查询里引用：
-
... WHERE id IN (SELECT id FROM tree)❌ -
... JOIN tree ON e.parent_id = tree.id✅
必须有终止条件，否则可能卡死或超限（默认最多 1000 层）：
WITH RECURSIVE org AS ( SELECT id, name, parent_id, 1 AS level FROM departments WHERE parent_id IS NULL UNION ALL SELECT d.id, d.name, d.parent_id, org.level + 1 FROM departments d INNER JOIN org ON d.parent_id = org.id WHERE org.level < 5 -- 显式限制深度 ) SELECT * FROM org;
别把原嵌套子查询整段搬进 AS ()
CTE 的价值不在“搬代码”，而在拆逻辑、重关联。典型翻车点：
- 原写法：
WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.dept_id = e1.dept_id) - 错误搬法：
dept_avg AS (SELECT AVG(salary) FROM employees e2 WHERE e2.dept_id = e1.dept_id)→e1在 CTE 里不可见 - 正确拆法：
dept_avg AS (SELECT dept_id, AVG(salary) AS avg_salary FROM employees GROUP BY dept_id)，再JOIN关联
字段类型也要对齐：比如 dept_id 是 BIGINT，而 CTE 里用了 INT，JOIN 时可能触发隐式转换，拖慢性能。
CTE 不是“换汤不换药”的语法糖，它是 MySQL 8.0+ 处理复杂查询的默认路径——但前提是真正按它的规则拆解逻辑，而不是只把括号挪个位置。最容易被忽略的是：CTE 生命周期仅限当前语句，不能跨语句复用；想全局复用，得用视图，不是 CTE。
