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;