Mysql8 分组排序取第一个

准备

create table test.class
(
    class_id int auto_increment
        primary key,
    name     varchar(20) null
);

create table test.student
(
    student_id   int auto_increment
        primary key,
    student_name varchar(20) null,
    class_id     int         null,
    score        int         null
);

student_id	student_name	class_id	score
1	大王	1	9
2	小王	1	9
3	大小王	2	9

根据班级取分数第一个的

1. RANK()

select *
from (select *, RANK() over (PARTITION BY class_id order by student.score DESC) ranks from student) as f
where f.ranks = 1;

结果：

student_id	student_name	class_id	score	ranks
1	大王	1	9	1
2	小王	1	9	1
3	大小王	2	9	1

RANK()会把得分相同的都显示

2.ROW_NUMBER()

select *
from
(select *, ROW_NUMBER() over (PARTITION BY class_id order by student.score DESC) rn from student)
    as f
where rn = 1

结果:

student_id	student_name	class_id	score	rn
1	大王	1	9	1
3	大小王	2	9	1