cte比子查询更适合复杂分组逻辑，因其可命名复用中间结果、避免嵌套过深和多层子查询兼容性问题，并支持递归处理树形结构。
CTE 为什么比子查询更适合复杂分组逻辑
因为 CTE 能把中间结果命名、复用，避免嵌套过深导致的可读性崩溃和 MySQL 5.7 以下版本不支持多层子查询的问题。子查询一旦要重复引用同一份加工数据（比如先算用户等级，再按等级分组统计），就得写两遍甚至三遍，改一处漏一处。
- CTE 支持递归，适合树形结构（如组织架构、评论回复链）的层级聚合
- PostgreSQL / SQL Server / MySQL 8.0+ 原生支持；SQLite 3.8.3+ 也行，但不支持递归
- CTE 本身不物化，性能取决于底层查询——别在
WITH里塞SELECT *再过滤，该加索引还得加
怎么写一个带条件预处理的 CTE 分组查询
典型场景：订单表里有 status、amount、created_at，你想先筛出“近30天已支付订单”，再按“是否新客”分组统计金额和单数。直接在 GROUP BY 里写 CASE WHEN 会重复判断，也不方便后续加维度。
WITH paid_recent AS (
SELECT
order_id,
amount,
user_id,
CASE WHEN first_order_time IS NOT NULL THEN 'new' ELSE 'old' END AS customer_type
FROM orders o
LEFT JOIN users u ON o.user_id = u.id
WHERE status = 'paid'
AND created_at >= CURRENT_DATE - INTERVAL '30 days'
)
SELECT
customer_type,
COUNT(*) AS cnt,
SUM(amount) AS total_amount
FROM paid_recent
GROUP BY customer_type;
-
WHERE条件必须放在 CTE 内部，而不是主查询——否则可能漏掉 CTE 中被过滤掉的关联数据 -
CASE WHEN提前算好标签，比在GROUP BY里重复写更安全，也方便后续加HAVING过滤 - 别在 CTE 里用
ORDER BY或LIMIT——它们对最终分组没意义，还可能干扰优化器
多个 CTE 怎么串起来做分步清洗
当逻辑涉及“去重→补缺→打标→聚合”多步骤时，用逗号分隔多个 CTE 最清晰。比如用户行为日志中，先取每个用户的首条访问，再关联用户画像，最后按地域+设备分组。
WITH first_visit AS (
SELECT user_id, MIN(event_time) AS first_time
FROM events
GROUP BY user_id
),
enriched AS (
SELECT
fv.user_id,
u.region,
u.device_type
FROM first_visit fv
JOIN users u ON fv.user_id = u.id
)
SELECT region, device_type, COUNT(*)
FROM enriched
GROUP BY region, device_type;
- 每个 CTE 只做一件事，命名要直白（
first_visit比t1强十倍） - 后一个 CTE 可以引用前面所有已定义的 CTE，但不能跨过定义顺序引用（
enriched不能引用还没出现的summary） - 如果某步需要临时索引加速（比如大表
JOIN），得靠物理表或物化视图——CTE 不存数据
常见报错和兼容性陷阱
WITH 开头报错？大概率是数据库版本太低或者语法位置不对。MySQL 8.0 前不支持 CTE，而 SQL Server 要求 WITH 必须是批处理第一条语句（前面不能有 DECLARE 或空行）。
- 错误信息
ERROR 1248 (42000): Every derived table must have its own alias：这是把 CTE 当成子查询用了，忘了 CTE 不需要括号和别名 - PostgreSQL 报
relation "xxx" does not exist：CTE 名称大小写敏感，且不能和真实表同名（即使加了 schema 也不行） - CTE 中用了窗口函数（如
ROW_NUMBER()），又想在主查询里再GROUP BY——没问题，但注意窗口函数执行优先级高于GROUP BY，别指望它能“按分组后结果编号”
真正难的不是写 CTE，而是判断哪一步该提前算、哪一步该留到主查询里做——尤其是涉及 DISTINCT 和 HAVING 的时候，少一次扫描，就少一次潜在的数据倾斜。
