cte核心作用是提取需先算、命名、复用的逻辑，非简化join语法；适用场景包括子查询被join和where同时引用、嵌套≥3层、含group by+join+时间过滤且具明确业务语义的子查询。
MySQL 8.0 中 CTE 不是用来“替换任意子查询”的，而是把那些该先算、该命名、该复用的逻辑拎出来——否则嵌套三层以上，FROM (SELECT FROM (SELECT FROM ...)) 一缩进，括号都对不上。
哪些嵌套子查询真该用 CTE 拆？
不是所有嵌套都值得改。真正该拆的只有这几种：
- 同一子查询在主查询里被
JOIN和WHERE同时引用（比如既要关联用户最近订单，又要过滤「有订单」的用户） - 嵌套深度 ≥3 层，例如
FROM (SELECT * FROM (SELECT * FROM (SELECT ...))) t，已经缩进到看不见括号了 - 子查询含
GROUP BY+JOIN+ 时间过滤，且具备明确业务语义（如latest_order_per_user这种名字能直接替代一整段子查询）
CTE 名字怎么起才不翻车？
名字不是占位符，是调试时的第一眼线索。名字要反映数据状态，而不是操作动作：
- ✅
active_users_2024—— 表明「谁」+「什么时间范围」+「什么状态」 - ❌
subq1或tmp_join—— 调试时根本不知道它在哪一步塌了 - ⚠️
user_orders_with_status—— 名字太长且模糊，不如拆成shipped_orders和user_totals两个独立 CTE
LEFT JOIN 后的条件写错位置，CTE 也救不了你
这是线上最常翻车的点：把右表筛选条件写在主查询 WHERE 里，等于把 LEFT JOIN 变成 INNER JOIN。CTE 拆得再清楚也没用。
错误写法：SELECT u.name FROM users u LEFT JOIN orders o ON u.id = o.user_id WHERE o.status = 'paid' → 所有没订单或订单非 paid 的用户全丢掉
正确写法：LEFT JOIN orders o ON u.id = o.user_id AND o.status = 'paid' → 左表用户全保留，右表只连上 status='paid' 的订单
CTE 里的写法同理：如果在 WITH paid_orders AS (SELECT * FROM orders WHERE status = 'paid') 里提前过滤，后面 LEFT JOIN paid_orders 就安全；但如果漏了 WHERE，又在主查询 WHERE 补，照样退化。
MySQL 5.7 用户别硬上 CTE
WITH 关键字在 MySQL 5.7 及更早版本不识别，会直接报错 ERROR 1064 (42000)。这时候强行改写只会浪费时间。
替代方案一：CREATE TEMPORARY TABLE 手动建中间表，记得加索引；
替代方案二：用视图（CREATE VIEW），适合固定逻辑且需多次复用的场景。
CTE 的轻量性只在 8.0+ 生效——它不落盘、不锁表、执行完即销毁。但前提是，你得在对的版本上用它。
