mysql查询各班最高分学生的信息 MySQL

学生表student

文章图片

班级表class

文章图片

课程表subject

文章图片

成绩表score

文章图片

一、查询各班最高分学生的信息
1.从成绩表score中查询每个学生的总成绩并按降序排列

select sc.stu_id,sum(sc.score) sumscore from score sc group by sc.stu_id order by sumscore desc

文章图片

2. 用1得到的表和学生表student联合查询得到学生个人信息

select stu.class_id,stu.name,stu.sex,a.sumscore from student stu inner join (select sc.stu_id,sum(sc.score) sumscore from score sc group by sc.stu_id order by sumscore desc) a on stu.id = a.stu_id

文章图片

3.用group by按class_id字段进行分组

select stu.class_id,stu.name,stu.sex,a.sumscore from student stu inner join (select sc.stu_id,sum(sc.score) sumscore from score sc group by sc.stu_id order by sumscore desc) a on stu.id = a.stu_id group by stu.class_id

文章图片

4.和班级表class联合查询得到结果

select aa.name,aa.sex,cla.classname,aa.sumscore from (select stu.class_id,stu.name,stu.sex,a.sumscore from student stu inner join (select sc.stu_id,sum(sc.score) sumscore from score sc group by sc.stu_id order by sumscore desc) a on stu.id = a.stu_id group by stu.class_id) aa inner join class cla on cla.classid = aa.class_id;