cte比嵌套子查询更易读，因其用with提前定义命名逻辑块，避免多层括号嵌套和别名冲突；适合改写的三类场景是：子查询被多次引用、本身复杂、需递归；需注意语法细节如列名显式声明、多cte用逗号分隔、不可省略换行等。
为什么CTE比嵌套子查询更易读
嵌套子查询写到三层以上，SQL就变成“俄罗斯套娃”：括号层层包裹，字段别名容易冲突，调试时根本分不清哪个WHERE对应哪层SELECT。CTE用WITH提前定义逻辑块，相当于给子查询起个名字、单独抽出来，后续主查询直接引用，结构一目了然。
CTE改写必须满足的三个条件
不是所有子查询都能无脑套WITH。以下情况才适合改写：
- 子查询被多次引用（比如在
SELECT和WHERE里都用了同一段聚合逻辑） - 子查询本身较复杂（含
JOIN、窗口函数、多层GROUP BY） - 需要递归（如组织树、路径遍历），这时只能用
WITH RECURSIVE
如果子查询只用一次且就一行SELECT COUNT(*) FROM ...，硬套CTE反而多此一举。
改写时最容易漏掉的语法细节
CTE不是“变量”，它不保存结果集，每次被引用都会重新执行。这点常被忽略，导致性能反降：
-
WITH后不能跟;，但CTE定义和主查询之间要换行，不能写成WITH t AS (...) SELECT * FROM t;连在一起（部分数据库会报错） - 多个CTE用逗号分隔，不是
UNION或AND：WITH a AS (...), b AS (...)✔️；WITH a AS (...) AND b AS (...)❌ - CTE里的列名必须显式声明，不能依赖子查询的
AS别名：写成WITH t(col1, col2) AS (SELECT x, y FROM ...)，而不是只靠SELECT x AS col1
一个典型改写示例（PostgreSQL/MySQL 8.0+）
原嵌套写法：
SELECT u.name,
(SELECT AVG(amount) FROM orders o WHERE o.user_id = u.id) avg_order,
(SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id AND o.status = 'paid') paid_count
FROM users u;
改写为CTE：
WITH user_stats AS (
SELECT user_id,
AVG(amount) AS avg_order,
COUNT(*) FILTER (WHERE status = 'paid') AS paid_count
FROM orders
GROUP BY user_id
)
SELECT u.name, s.avg_order, s.paid_count
FROM users u
LEFT JOIN user_stats s ON u.id = s.user_id;
注意：COUNT(*) FILTER是PostgreSQL语法，MySQL需改用SUM(IF(status='paid',1,0))；LEFT JOIN确保没订单的用户不丢失——这点在原子查询中是隐式实现的，改写时得主动补上。
CTE真正省力的地方不在语法，而在调试：你可以单独运行SELECT * FROM user_stats验证中间结果，而嵌套子查询只能全删重写。
