SQL如何统计每个分组的前N条记录_借助ROW_NUMBER窗口函数-SQL-PHP中文网

ROW_NUMBER()需配合PARTITION BY和ORDER BY才能实现分组排序编号；仅ORDER BY会全局编号，漏PARTITION BY将无法按组重排；正确语法为ROW_NUMBER() OVER(PARTITION BY 字段 ORDER BY 字段)。

sql如何统计每个分组的前n条记录_借助row_number窗口函数

ROW_NUMBER() 必须配合 PARTITION BY 和 ORDER BY 才能分组取前N

单独写 ROW_NUMBER() 不加 PARTITION BY 只会全局编号，根本不是“每个分组”。常见错误是只写 ORDER BY，漏掉 PARTITION BY，结果返回的全是 1～总数，而不是每组内从 1 开始重排。

正确结构是：ROW_NUMBER() OVER (PARTITION BY 分组字段 ORDER BY 排序字段)。比如按部门分组、按薪资降序取前3人，PARTITION BY dept_id 和 ORDER BY salary DESC 缺一不可。

排序字段建议有唯一性，否则相同值会导致 ROW_NUMBER() 随机分配序号（如多个同薪员工，谁排第1不确定）
如果业务要求“并列不跳号”，该用 RANK() 或 DENSE_RANK()，ROW_NUMBER() 严格按行物理顺序编号
MySQL 8.0+、PostgreSQL、SQL Server、Oracle 都支持；SQLite 3.25+ 支持，旧版不支持窗口函数

嵌套子查询是主流写法，CTE 更易读但非必须

因为 WHERE 无法直接引用窗口函数结果（执行顺序：WHERE → GROUP BY → HAVING → SELECT → ORDER BY），所以必须把 ROW_NUMBER() 放在子查询或 CTE 中，再在外层筛选 rn 。

示例（取每个部门薪资前2的员工）：

SELECT dept_id, name, salary
FROM (
  SELECT dept_id, name, salary,
         ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn
  FROM employees
) t
WHERE t.rn

CTE 写法更清晰，适合多层逻辑：WITH ranked AS (SELECT ..., ROW_NUMBER() ...) SELECT * FROM ranked WHERE rn
别名 rn 必须在子查询里定义，外层才能用；不能写成 WHERE ROW_NUMBER() ... ，语法报错
某些旧版 MySQL（

性能敏感时注意排序字段的索引覆盖

ROW_NUMBER() 的 ORDER BY 部分若涉及大表扫描且无索引，性能会断崖式下降。例如 PARTITION BY dept_id ORDER BY created_at DESC，理想索引是 (dept_id, created_at) 联合索引。

单列索引 dept_id 或 created_at 效果有限，优化器通常无法高效跳过分区内的全排序
如果排序字段是表达式（如 ORDER BY UPPER(name)），需建函数索引（PostgreSQL/MySQL 8.0.13+ 支持），否则必走文件排序
分组数极多（如百万级用户按 user_id 分组）+ 每组取前N，即使有索引也可能内存溢出，考虑加 LIMIT 或分批处理

NULL 值参与排序时行为要主动控制

ORDER BY 中遇到 NULL 默认排在最前（ASC）或最后（DESC），不同数据库略有差异（如 PostgreSQL 默认 NULLS LAST，MySQL 默认 NULLS FIRST）。若字段可能为空，且你希望空值不参与排名或统一归到末尾，得显式声明。

安全写法：ORDER BY salary DESC NULLS LAST（PostgreSQL/Oracle），MySQL 不支持该语法，需用 ORDER BY IFNULL(salary, -999999) DESC 替代
如果空值本应排除，先在子查询 WHERE 中过滤：WHERE salary IS NOT NULL，比在排序里兜底更明确
分组字段本身为 NULL 会被视为同一组——这点容易被忽略，如有 dept_id IS NULL 的记录，它们全挤在“空部门”里一起排名

实际跑通的关键就三点：分组和排序字段写全、窗口结果必须套一层再过滤、NULL 和索引问题提前想好。没跑出来先查是不是漏了 PARTITION BY，或者外层 WHERE 直接用了窗口函数名。