mssql中如何使用row_number和partition by分组取排名前3的数据

在mssql的查询场景中，我们经常会遇到需要按某个字段分组，再在每个分组内按指定规则排序，最后取出每组排名前N条数据的需求，其中分组取排名前3的数据是非常典型的场景，通过row_number窗口函数结合partition by子句可以高效实现这个需求。

核心语法说明

要实现分组排名，首先需要了解两个关键语法的用法：

• partition by：用于指定分组的字段，窗口函数会按照该字段的值将数据划分为多个独立的分组，每个分组内的计算互不影响。
• row_number()：是mssql的窗口函数之一，会为每一行数据生成一个唯一的序号，序号从1开始，按照order by子句指定的排序规则递增。

两者结合的基础语法结构如下：

row_number() over(partition by 分组字段 order by 排序字段 [asc|desc]) as 排名别名

示例准备

为了更直观地演示效果，我们先创建一个测试表并插入测试数据，表结构用来存储不同班级的学生成绩信息：

-- 创建学生成绩表
create table student_score(
    id int identity(1,1) primary key,
    class_name varchar(20), -- 班级名称
    student_name varchar(20), -- 学生姓名
    score int -- 考试成绩
);

-- 插入测试数据
insert into student_score(class_name, student_name, score) values
('一班', '张三', 92),
('一班', '李四', 88),
('一班', '王五', 95),
('一班', '赵六', 85),
('二班', '钱七', 90),
('二班', '孙八', 93),
('二班', '周九', 87),
('二班', '吴十', 91),
('三班', '郑十一', 89),
('三班', '王十二', 94),
('三班', '李十三', 91);

实现分组取排名前3数据

现在需求是获取每个班级考试成绩排名前3的学生信息，我们可以按照下面的步骤编写查询语句：

第一步：生成分组排名

先使用row_number结合partition by按班级分组，再按成绩降序排序，生成每个学生的班级内排名：

select 
    class_name,
    student_name,
    score,
    row_number() over(partition by class_name order by score desc) as class_rank
from student_score;

这段查询会返回所有学生的信息，同时新增class_rank字段，代表该学生在班级内的成绩排名，分数越高排名越靠前。

第二步：筛选排名前3的数据

由于窗口函数生成的排名不能直接用在where子句中，我们需要将上面的查询结果作为子查询，再筛选class_rank小于等于3的记录：

select 
    class_name,
    student_name,
    score,
    class_rank
from (
    select 
        class_name,
        student_name,
        score,
        row_number() over(partition by class_name order by score desc) as class_rank
    from student_score
) as temp
where class_rank <= 3
order by class_name, class_rank;

结果说明

上述查询的最终结果如下：

注意事项

• 如果分组内存在相同排序值的记录，row_number会随机分配不同的序号，不会给相同值的记录相同的排名。如果需要相同值排名相同，可以使用rank或者dense_rank窗口函数。
• partition by子句可以指定多个分组字段，比如需要按班级和科目分组时，可以写成partition by class_name, subject_name的形式。
• 排序规则如果不指定asc或desc，默认是asc升序，排名前3会是分数最低的3条，需要根据实际需求选择排序方向。

使用row_number和partition by的方式比传统的子查询分组取前N条数据的方式性能更好，尤其是数据量较大的场景下，推荐优先使用窗口函数实现分组排名需求。

mssqlrow_numberpartition_by分组排名修改时间：2026-07-22 20:45:33