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;