SQL-查询单科成绩最高的学生信息

一 表结构如下

create table class(
    id int auto_increment primary key,
    name varchar(10),
    score varchar(10),
    subject varchar(10)
)

insert into class values(default,'张三',60,'数学');
insert into class values(default,'张三',70,'英语');
insert into class values(default,'李四',90,'数学');
insert into class values(default,'李四',20,'英语');
insert into class values(default,'王五',70,'数学');
insert into class values(default,'王五',90,'英语');

二 数据查询

select * from class;

image.png

三. 查询单科成绩最高的学生信息

1. -- 首先查询 单科最高分

select subject,MAX(score) from class group by subject; 

image.png

2.-- 单科最高分信息(错误)

select subject,max(score),name from class group by subject;

可以看到下图，查询结果并不对

image.png

3.-- 单科最高分信息(方式一)

select subject,score,name from class where (subject,score) in 
(select subject,MAX(score) from class group by subject);

image.png

4.-- 单科最高分信息(方式二)

select c.* from  
(select subject,MAX(score) score from class group by subject) d,class c
where d.subject=c.subject and  d.score=c.score 

image.png

参考：
https://www.cnblogs.com/geaozhang/p/6839297.html
https://blog.csdn.net/u010827070/article/details/79712303