必须用CTE先算出窗口结果再在外层WHERE过滤，因窗口函数值在SELECT阶段才生成，WHERE执行时不存在；CTE需分两层写，强过滤条件须放入CTE内部以利用索引，避免全表计算后丢弃。
必须用 CTE 先算出窗口结果，再在外层 WHERE 过滤——窗口函数值在 SELECT 阶段才生成，WHERE 执行时它根本不存在。
CTE 必须分两层写，不能在 WITH 里直接 WHERE 窗口别名
很多人写成这样会报错：
WITH t AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY score DESC) AS rn FROM students WHERE rn <= 3 -- ❌ 错误：rn 还没定义，WHERE 不认识它 ) SELECT * FROM t;
正确结构是：CTE 只负责计算 + 命名，过滤动作必须挪到 CTE 之后的主查询中。
- CTE 内只放
SELECT+ 窗口函数 + 必要的WHERE（用于提前过滤原始数据，不是窗口结果） - 主查询从 CTE 引用，并用
WHERE对窗口别名（如rn）做条件 - CTE 名称后不能跟
WHERE或ORDER BY，那是主查询的事
CTE 内部要先过滤再开窗，否则性能爆炸
如果原始表很大，但你只关心最近一周的数据，却把时间过滤放在外层：
WITH ranked AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn FROM employees -- ❌ 全表扫描，哪怕最终只取 10 行 ) SELECT * FROM ranked WHERE rn = 1 AND created_at >= '2026-08-01';
数据库得先给几百万行都算一遍 ROW_NUMBER()，再扔掉 99.9%。正确做法是把强过滤条件塞进 CTE 内部：
WITH ranked AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn FROM employees WHERE created_at >= '2026-08-01' -- ✅ 先筛再算，索引能用上 ) SELECT * FROM ranked WHERE rn = 1;
- 确保过滤字段（如
created_at）有索引，且和ORDER BY字段组成联合索引 - MySQL 8.0 默认不物化 CTE，这步优化效果明显；PostgreSQL 则需留意是否被强制物化（查
EXPLAIN） - 别依赖 CTE 自动“聪明”，它只是语法结构，不改变执行逻辑
用 RANK() 或 DENSE_RANK() 代替 ROW_NUMBER() 时，WHERE 行为完全不同
同一个 CTE，换一个窗口函数，WHERE rn = 1 的结果可能从 1 行变成 5 行：
WITH ranked AS ( SELECT *, RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rnk FROM employees ) SELECT * FROM ranked WHERE rnk = 1;
-
ROW_NUMBER()：严格按顺序编号，WHERE rn = 1每组只返回 1 行 -
RANK()：并列时同号、跳号，WHERE rnk = 1可能返回多行（比如两人同为最高薪） -
DENSE_RANK()：并列时同号、不跳号，WHERE drnk = 1也返回所有并列第一 - 如果业务要求“每组只取一条”，必须用
ROW_NUMBER()；如果允许并列，则选后两者
PostgreSQL 要小心 CTE 物化陷阱，MySQL 8.0 相对安全
PostgreSQL 默认把每个 CTE 当作物化临时表处理，即使你只引用一次。这意味着：
- CTE 中用了窗口函数，主查询又 JOIN 或子查询引用它 → 可能触发多次全表扫描
- 解决方法：显式加
MATERIALIZED或NOT MATERIALIZED（PG 12+），或直接改用子查询 - MySQL 8.0 的 CTE 更接近语法糖，默认不强制物化，链式 CTE（如先聚合再排名）更可靠
- 无论哪种数据库，都要用
EXPLAIN看实际执行计划，别信“写了 CTE 就一定快”
真正关键的不是 CTE 本身，而是你把哪部分逻辑放进 CTE —— 提前过滤、避免重复计算、明确分层意图，比语法形式重要得多。
