mysql 统计学生成绩-面试题

最新推荐文章于 2024-09-08 15:49:52 发布

从兄

最新推荐文章于 2024-09-08 15:49:52 发布

阅读量2.2k

点赞数

分类专栏： mysql 面试文章标签： mysql 数据库

本文链接：https://blog.csdn.net/qq_33212312/article/details/116009762

版权

mysql 同时被 2 个专栏收录

12 篇文章 0 订阅

订阅专栏

面试

2 篇文章 0 订阅

订阅专栏

先创建三个表学生表、成绩表、科目表

1、查询单个科目成绩结果如下图

select stu_id, score from score where subject_id = 1001;

2、查询学生总成绩结果如下图

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

3、查询学生各科成绩和总成绩，在一张表上显示出来，总成绩高的在前

select a1.stu_id, a1.score '数学', 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 a1.stu_id = a3.stu_id
left join (select stu_id, sum(score) sumscore from score group by stu_id) 
b on b.stu_id = a1.stu_id order by b.sumscore desc