MySQL窗口函数按分组取排名前N条的查询设计

在部门榜单、门店商品排行或每个租户的最新记录中，经常需要“每组只保留前 N 条”。MySQL 的窗口函数适合处理这个问题：先用 PARTITION BY 划分分组，再用 ORDER BY 计算组内名次，最后在外层查询筛选排名。这样既能保留原始行，也能明确处理并列数据。

官方地址：https://dev.mysql.com/doc/refman/8.4/en/window-functions-usage.html

先把分组、排序和名次计算拆成两层

下面用员工薪资表表示一个小型部门排行榜。真正决定“前 N”的不是表名，而是三个选择：department_id 是分组键，salary 是主要排序键，employee_id 是同薪资时的稳定次序。若漏掉最后这个唯一键，同一批并列记录在数据变化后可能交换位置。

-- 先在每个部门内编号，再由外层筛出每组前两名
WITH ranked AS (
    SELECT
        employee_id,
        department_id,
        employee_name,
        salary,
        ROW_NUMBER() OVER (
            PARTITION BY department_id
            ORDER BY salary DESC, employee_id ASC
        ) AS row_no
    FROM employee_salary
)
SELECT department_id, employee_id, employee_name, salary, row_no
FROM ranked
WHERE row_no 

窗口表达式只负责给每一行附加排名，外层 WHERE 才负责裁剪结果。不能在同一层直接写 WHERE ROW_NUMBER() OVER (...) ，把计算层和筛选层分开更容易扩展为动态 N、增加字段或继续关联其他表。

ROW_NUMBER、RANK 和 DENSE_RANK 的边界要先选清

“排名前两条”有两种常见含义：每组最多两行，或每组保留前两名且并列者全部留下。三种函数的差异可以先用这张表判断：

例如同一部门的薪资为 10000、9000、9000、8000：使用 ROW_NUMBER() 只返回两行；使用 RANK() 会返回三行；使用 DENSE_RANK() 也会返回三行，但名次连续。并列业务规则必须写进需求，否则查询看似正确，结果数量却会让调用方误判。

-- 并列员工都要保留时，按“名次”而不是“行号”过滤
WITH ranked AS (
    SELECT
        department_id,
        employee_id,
        employee_name,
        salary,
        RANK() OVER (
            PARTITION BY department_id
            ORDER BY salary DESC
        ) AS salary_rank
    FROM employee_salary
)
SELECT department_id, employee_id, employee_name, salary, salary_rank
FROM ranked
WHERE salary_rank 

把动态 N、空值和性能检查写进查询设计

如果 N 来自接口参数，先在应用层把它限制为正整数，再绑定到外层条件；不要把未经约束的字符串拼接进 SQL。排序列含有 NULL 时，也要明确它在业务上应该排在前面还是后面，必要时写成显式的条件排序，例如 ORDER BY (salary IS NULL), salary DESC, employee_id。

窗口查询通常需要按分组键和排序键组织数据。索引可以作为输入过滤的辅助，但不能简单认为“建了索引就一定避免排序”。应使用 EXPLAIN 查看扫描行数、临时表和排序信息；先在外层缩小日期或租户范围，再做窗口排名，往往比对全表排名更稳。

-- 先限制统计周期，减少参与窗口计算的数据量
WITH ranked AS (
    SELECT
        department_id,
        employee_id,
        employee_name,
        salary,
        ROW_NUMBER() OVER (
            PARTITION BY department_id
            ORDER BY salary DESC, employee_id
        ) AS row_no
    FROM employee_salary
    WHERE salary_month = '2026-09'
)
SELECT department_id, employee_id, employee_name, salary
FROM ranked
WHERE row_no 

验收时至少核对三件事：每个分组的返回数量是否符合并列规则，排序相同时是否仍能稳定复现，日期或租户过滤是否在窗口计算前生效。遇到结果比预期多，不要先删掉并列行，先确认使用的是“前 N 行”还是“前 N 名”。

常见问题

为什么不能直接在 WHERE 中使用窗口函数？

窗口函数为结果行附加计算值，而同层 WHERE 的筛选发生在它之前。使用 CTE 或派生表先生成排名，再由外层过滤，是更清晰且可组合的写法。

怎样保证每组固定返回 N 行？

使用 ROW_NUMBER()，并在窗口排序中追加主键或唯一键。若业务要求保留并列名次，则不能承诺固定行数，应改用 RANK() 或 DENSE_RANK() 并接受结果可能超出 N。

RANK 和 DENSE_RANK 该怎么选？

看并列后是否需要跳号：奖项名次通常保留跳号语义，层级筛选通常更关心连续的不同分值档位。