row_number()不能直接在where中过滤，因为where执行早于窗口函数；必须用cte或子查询先生成排名列（如rn），再在外层where中筛选，如where rn
CTE里写ROW_NUMBER()后，外层WHERE怎么过滤排名
窗口函数不能出现在 WHERE 子句中，这是执行顺序决定的：SQL 先跑 WHERE，再算 SELECT 里的窗口函数。所以必须用 CTE 或子查询先把排名固化成一列，才能在外层筛。
正确结构是：
WITH ranked AS (SELECT *, ROW_NUMBER() OVER (ORDER BY sales DESC) AS rn FROM orders)SELECT * FROM ranked WHERE rn
别写成 WHERE ROW_NUMBER() OVER (...) —— 直接报错 <code>column "rn" does not exist 或语法错误。
为什么用ROW_NUMBER()而不是RANK()来取“严格前三条”
如果只要固定 3 条记录（比如发奖名单），ROW_NUMBER() 是唯一稳妥选择。它强制给每行唯一编号，哪怕 sales 值相同，也会按隐式顺序（如主键、插入顺序）分出先后。
RANK() 和 DENSE_RANK() 在并列时会给出相同名次，导致结果行数不可控：
- 3 人并列第 1 →
RANK()返回 1,1,1 → 外层WHERE rnk 拿到全部 3 行 ✅，但没“第 2 名”“第 3 名”概念 - 2 人并列第 1，1 人第 3 →
RANK()是 1,1,3 →WHERE rnk 拿到 3 行 ✅ - 但若 4 人并列第 1 →
RANK()是 1,1,1,1 →WHERE rnk 仍返回 4 行 ❌
所以“取前 N 条记录”场景，优先用 ROW_NUMBER()；“取名次 ≤ N 的所有用户”才考虑 RANK()。
PARTITION BY 分组排名时，CTE里漏写ORDER BY会怎样
PARTITION BY dept ORDER BY sales DESC 中的 ORDER BY 不可省略。不写就等于没排序，ROW_NUMBER() 会按数据库内部任意顺序编号，每次执行结果可能不同，尤其在并发或数据变更后。
常见错误写法：ROW_NUMBER() OVER (PARTITION BY dept) —— 看似合法，实则危险。
正确做法：
- 显式写全
ORDER BY，例如ORDER BY sales DESC, staff_id ASC，用staff_id消除并列不确定性 - 确保排序字段有索引，否则
OVER子句性能会明显下降 - 别用
ORDER BY hire_date这类可能重复的字段单独排序，加二级排序字段兜底
CTE比嵌套子查询好在哪，什么情况下反而更慢
CTE 主要优势是逻辑清晰、可读性强、支持复用（比如同一 CTE 后续既查 Top3 又算平均值）。但 CTE 不是性能优化银弹。
注意这些坑：
- PostgreSQL 和 SQL Server 通常能将 CTE 内联优化，但 MySQL 8.0 对 CTE 默认物化（临时表），大数据量时可能比等价子查询更慢
- 如果 CTE 里做了聚合（如
SUM() GROUP BY），又在外层 JOIN 其他大表，容易触发笛卡尔积或重复计算 - 过度嵌套 CTE（比如 A → B → C → 主查询）会让优化器放弃代价估算，直接走低效计划
简单排名过滤用 CTE 没问题；涉及多层聚合+关联时，先 EXPLAIN 看执行计划，必要时拆回单层子查询或加索引。
