River Flows In You
0%

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

发表于 更新于 分类于

##一 表结构如下

1
2
3
4
5
6
7
8
9
10
11
12
13
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,'英语');

##二 数据查询

1
select * from class;

image.png

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

  1. – 首先查询 单科最高分
    1
    select subject,MAX(score) from class group by subject;
    image.png

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

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

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

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

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

image.png

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

1
2
3
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