mysql查询每个学生最高分_mysql查询各班最高分学生的信息

  • Post author:
  • Post category:mysql


学生表student

d3b69774dc7c5f602918612d27575ecc.png

班级表class

dfd8c2e64c0e290da521b1596920d17a.png

课程表subject

683d438f3aa44c9527b02afcd00348aa.png

成绩表score

7328356bb1d8caf0dd97a09d0a7ae844.png

一、查询各班最高分学生的信息

1.从成绩表score中查询每个学生的总成绩并按降序排列

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

11c8ec1a61dc57a1ac9b10271d9537c9.png

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

bb0da319d353ed697567d0bac28be009.png

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

b1b9ed1b4665928ab0fe98d20b36a4e2.png

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;

55debc17e3b241c6689b9e7f0c53fdc7.png



版权声明:本文为weixin_34617955原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。