学生表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;
