SQL如何解决Top N问题的多种写法比较_ROW

ROW_NUMBER()是分组Top-N首选，因其严格编号、无跳号并列，语义明确且主流数据库兼容；但需MySQL 8.0+、联合索引支持及子查询包裹过滤。

sql如何解决top n问题的多种写法比较_row_number最优解

ROW_NUMBER() 是解决分组 Top-N 问题最可靠、最通用的写法，但它的“最优”是有前提的：必须配合合适的索引，且不能滥用在低版本 MySQL 或不支持窗口函数的场景里。

它语义明确：对每个分组内数据严格编号，1、2、3……N，不会跳号也不会并列占位。适合需要“精确取 N 条”的业务，比如“每个部门薪资前三的员工”，哪怕第 3 名有并列，也只取排在前三位的三条记录。

主流数据库都支持：ROW_NUMBER() 在 PostgreSQL、SQL Server、Oracle、MySQL 8.0+、Trino、BigQuery 中行为一致；而 RANK() 和 DENSE_RANK() 在并列处理上逻辑不同，容易引发业务误解。

关键限制是：它必须用子查询或 CTE 包裹，再在外层用 WHERE 过滤，否则会报错——因为 WHERE 执行早于 SELECT，无法引用窗口函数生成的别名。

窗口函数本身不慢，慢的是没走索引导致全表扫描 + 内存排序。真正影响性能的是 PARTITION BY 和 ORDER BY 列的联合索引。

必须建联合索引，顺序为 (group_col, sort_col)，例如 (department_id, salary)
避免在 ORDER BY 中用函数，如 ORDER BY UPPER(name) —— 索引失效
如果 ORDER BY 多字段，索引需覆盖全部，如 ORDER BY salary DESC, id ASC → 索引应为 (department_id, salary DESC, id ASC)
MySQL 8.0+ 的执行计划里若出现 Using filesort，大概率说明索引未命中

这些版本不支持窗口函数，强行写会报错 FUNCTION xxx.ROW_NUMBER does not exist。此时常见替代方案有：

相关子查询（性能差）：SELECT * FROM t1 WHERE (SELECT COUNT(*) FROM t2 WHERE t2.group_id = t1.group_id AND t2.val > t1.val) —— 数据量过万就明显卡顿
自连接模拟排名（可读性差）：需对每组做左连接计数，SQL 冗长且易出错
LIMIT + UNION ALL（仅限 group 数少）：手动为每个 group 写一条 SELECT ... ORDER BY ... LIMIT N，再 UNION ALL 合并 —— group 多时不可维护
根本解法：升级到 MySQL 8.0+。实测同样数据下，ROW_NUMBER() 比子查询快 5–10 倍

如果只要每个分组的第一条（比如“每个城市最新的一条订单”），DISTINCT ON 比 ROW_NUMBER() 更轻量：

SELECT DISTINCT ON (city_id) *
FROM orders
ORDER BY city_id, created_at DESC;

它不触发窗口排序步骤，执行计划常显示为 Index Scan + Unique，速度极快。但有两个硬约束：

真正容易被忽略的是：即使用了 DISTINCT ON，仍需 (city_id, created_at) 联合索引，否则 ORDER BY 仍会触发全局排序。

SQL如何解决Top N问题的多种写法比较_ROW_NUMBER最优解