CTE比嵌套子查询更适合复杂分组逻辑，因其能显式命名中间结果（如dept_stats）、分步表达聚合意图，避免字段引用错误和GROUP BY错乱，提升可读性与可维护性。
CTE为什么比嵌套子查询更适合复杂分组逻辑
因为CTE能显式命名中间结果，让每一步的聚合意图清晰可读，避免SQL变成“俄罗斯套娃”——特别是当你要先按部门统计人数、再算人均销售额、最后筛选Top 3部门时，嵌套子查询会让GROUP BY和ORDER BY层级错乱，调试起来得靠猜。
常见错误现象：ERROR: column "xxx" does not exist，本质是外层引用了内层未SELECT出来的字段；或者GROUP BY列漏写导致PostgreSQL报错，而MySQL在sql_mode宽松时悄悄返回错误结果。
- CTE中每一步都必须有明确别名（如
dept_stats），后续引用才不会歧义 - 不能在同一个CTE里递归引用自己（除非用
RECURSIVE关键字，但那是另一类问题） - CTE只是逻辑视图，不物化数据；如果同一CTE被多次引用，多数数据库会重复执行（PostgreSQL 12+支持
MATERIALIZED提示，但需手动加）
怎么写一个带多级分组的CTE链
把“先分组聚合 → 再基于聚合结果计算 → 最后过滤排序”拆成三步，每步一个WITH子句，用AS连接。关键不是语法多炫，而是让每步输出字段名直白反映业务含义，比如total_revenue比sum(sales)更容易被后续步骤理解。
使用场景：电商后台要查“近30天各品类下销量前5的SKU，且该SKU所属店铺平均评分≥4.8”。
WITH category_sales AS (
SELECT category_id, sku_id, SUM(quantity) AS qty_sold
FROM orders o JOIN items i ON o.order_id = i.order_id
WHERE o.created_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY category_id, sku_id
),
top_sku_per_cat AS (
SELECT category_id, sku_id, qty_sold,
ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY qty_sold DESC) AS rn
FROM category_sales
),
qualified_stores AS (
SELECT DISTINCT store_id
FROM shops
WHERE rating >= 4.8
)
SELECT t.category_id, t.sku_id, t.qty_sold
FROM top_sku_per_cat t
JOIN items i ON t.sku_id = i.sku_id
JOIN qualified_stores q ON i.store_id = q.store_id
WHERE t.rn <= 5;- 每个CTE只做一件事：聚合、排名、过滤，不混用
GROUP BY和ROW_NUMBER() -
ROW_NUMBER()必须配OVER，且PARTITION BY列要和上层GROUP BY对齐，否则排名逻辑崩坏 - 最后主查询里不要漏掉JOIN条件——CTE本身不带约束，
JOIN写错会导致笛卡尔积
CTE和临时表选哪个
如果中间结果要被反复读取（比如同时用于JOIN和聚合），或者数据量超百万行，用CREATE TEMP TABLE更稳；CTE在PostgreSQL里默认每次引用都重算，性能可能翻倍下跌。
参数差异：WITH RECURSIVE只能用于递归CTE（如组织架构树遍历），普通CTE不支持；而临时表可以建索引、可以ANALYZE更新统计信息，CTE完全不行。
- 临时表生命周期绑定会话，断开就消失，不用手动清理
- CTE无法在存储过程里跨语句复用（比如PL/pgSQL中不能把CTE结果存到变量），临时表可以
- 某些旧版MySQL（
CTE真正难的不是写法，是判断哪一步该停下来做物化——比如当category_sales结果集有200万行，而你只用它算排名，那CTE没问题；但如果还要拿它和用户表、地址表连查三次，这时候就得掂量是不是该换成临时表加索引。
