mysql查询所有学生各科成绩按照总成绩降序排列

学生表student

班级表class

课程表subject

成绩表score

查询所有学生各科成绩按照总成绩降序排列。

1.获取单门课程的学生成绩

select sc.stu_id,sc.score from score sc where sc.subject_id=1001

2.获取所有学生的总成绩

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

3.一共有三门课程，分别获取这三门课程的学生成绩表然后和学生总成绩表联合按总成绩进行降序排列得到一张新表

select a1.stu_id,a1.score 'Java编程',a2.score '应用统计学',a3.score '数据库',b.sumscore '总成绩' from
(select sc.stu_id,sc.score from score sc where sc.subject_id=1001) a1
left join
(select sc.stu_id,sc.score from score sc where sc.subject_id=1002) a2
on a1.stu_id = a2.stu_id left join
(select sc.stu_id,sc.score from score sc where sc.subject_id=1003) a3
on a3.stu_id = a2.stu_id left join
(select sc.stu_id,sum(sc.score) sumscore from score sc group by sc.stu_id) b
on a3.stu_id = b.stu_id 
order by b.sumscore desc

 

4.和学生表联合查询得到学生姓名

select stu.name,c.Java编程,c.应用统计学,c.数据库,c.总成绩 from
(
select a1.stu_id,a1.score 'Java编程',a2.score '应用统计学',a3.score '数据库',b.sumscore '总成绩' from
(select sc.stu_id,sc.score from score sc where sc.subject_id=1001) a1
left join
(select sc.stu_id,sc.score from score sc where sc.subject_id=1002) a2
on a1.stu_id = a2.stu_id left join
(select sc.stu_id,sc.score from score sc where sc.subject_id=1003) a3
on a3.stu_id = a2.stu_id left join
(select sc.stu_id,sum(sc.score) sumscore from score sc group by sc.stu_id) b
on a3.stu_id = b.stu_id order by b.sumscore desc
) c
left join student stu on stu.id = c.stu_id;

得到题目要求的结果。