MySQL分组top N

create table t_score
(
    id      int auto_increment primary key,
    name    varchar(10) not null comment '姓名',
    subject varchar(10) not null comment '科目',
    score   int         not null comment '分数'
) comment '学生成绩表';

insert into t_score(name, subject, score)
VALUES ('小明', '语文', 50),
       ('小明', '数学', 50),
       ('小明', '英语', 50),
       ('小明', '体育', 60),
       ('小刚', '语文', 90),
       ('小刚', '数学', 80),
       ('小刚', '英语', 50),
       ('小刚', '体育', 60),
       ('小红', '语文', 66),
       ('小红', '数学', 77),
       ('小红', '英语', 88),
       ('小红', '体育', 60);

按照姓名分组，取分数最大的行

select a.*
from t_score a
where a.score = (select b.score from t_score b where b.name = a.name order by b.score desc limit 1)

select a.*
from t_score a
where a.score = (select max(b.score) from t_score b where b.name = a.name)

select a.*
from t_score a
where (select count(1) from t_score b where b.name = a.name and a.score < b.score) < 1

# 子查询这个条件理解为, b中没有比a大的，所以a的记录是最大的

按照姓名分组，取分数最小的行

select a.*
from t_score a
where a.score = (select b.score from t_score b where b.name = a.name order by b.score limit 1)

select a.*
from t_score a
where a.score = (select min(b.score) from t_score b where b.name = a.name)

select a.*
from t_score a
where (select count(1) from t_score b where b.name = a.name and a.score > b.score) < 1

# 子查询这个条件理解为, a中没有比b大的，所以a的记录是最小的

按照姓名分组，取每组id最小的(最先出现的)行

select a.*
from t_score a
where a.id = (select b.id from t_score b where b.name = a.name order by id limit 1);

按照姓名分组，取每组分数最小的2(或N)个行，结果按照分数大小正序

select a.*
from t_score a
where (select count(1) from t_score b where b.name = a.name and a.score < b.score) < 2
order by a.name, a.score;

按照姓名分组，取每组分数最大的2(或N)行，结果按照分数大小倒序

select a.*
from t_score a
where (select count(1) from t_score b where b.name = a.name and a.score > b.score) < 2
order by a.name, a.score desc;