sql查询某学生最高分是哪科和每科最高分对应的学生
-
创建一张表user, 字段有姓名(name) 科目(subject), 分数(fraction):
create TABLE user(id parimary key auto_increment, subject VARCHAR(20) NOT NULL, fraction INT(11) NOT NULL);
- 少了一个name字段,所以插入数据
alter table user add name VARCHAR(20) NOT NULL
插入数据
insert into user ( name, subject, fraction) values ('张三', '英语', 75), ('张三', '数学', 90), ('张三', '化学', 99), ('李四', '英语', 55), ('李四', '数学', 60), ('李四', '化学', 89);
`
- 查询所李四哪个分数最高
select * from user where fraction=(select min(fraction) from user where name='李四') and name='李四';
- 查询科目中的分数最高的同学
方法一
select * from (select subject, max(fraction) as maxFra from user group by subject) as t2
left join user as u on u.fraction=t2.maxFra and u.subject=t2.subject;