2020 Oracle、mysql经典例题二(如何把学生成绩竖着排列)

最新推荐文章于 2022-03-20 08:56:13 发布

AlikeLin

最新推荐文章于 2022-03-20 08:56:13 发布

阅读量212

点赞数 1

分类专栏： oracle+mysql 文章标签： 1024程序员节 oracle mysql

本文链接：https://blog.csdn.net/weixin_44124577/article/details/109256764

版权

本文介绍了如何使用Oracle和MySQL数据库将学生成绩以竖直方式排列的两种方法，详细阐述了操作过程，两种方法都能成功运行。

摘要由CSDN通过智能技术生成

Oracle、mysql经典例题二--如何把学生成绩竖着排列

本例题是基于 Oracle、mysql经典例题一(如何把表格数据竖着排列)题目升级的，没看过之前的可能会有些疑惑。
题目：3张表
Student表
在这里插入图片描述

Course表

Score表

如何把这3张表合成下图形式？
在这里插入图片描述

解决：
第一种：简单暴力的

SELECT 姓名,SUM(语文),SUM(数学),SUM(英语),SUM(历史) FROM(
SELECT studentName AS 姓名,score AS 语文,0 AS 数学,0 AS 英语,0 AS 历史 from Student st,Course co,Score sc where st.studentid=sc.studentid and co.CourseId=sc.CourseId
group by StudentName,score,sc.courseId having sc.CourseId='1'
UNION ALL
SELECT studentName AS 姓名,0 AS 语文,score AS 数学,0 AS 英语,0 AS 历史 from Student st,Course co,Score sc where st.studentid=sc.studentid and co.CourseId=sc.CourseId
group by StudentName,score,sc.courseId having sc.CourseId='2'
UNION ALL
SELECT studentName AS 姓名,0 AS 语文,0 AS 数学,score AS 英语,0 AS 历史 from Student st,Course co,Score sc where st.studentid=sc.studentid and co.CourseId=sc.CourseId
group by StudentName,score,sc.courseId having sc.CourseId='3'
UNION ALL
SELECT studentName AS 姓名,0 AS 语文,0 AS 数学,0 AS 英语,score AS 历史 from Student st,Course co,Score sc where st.studentid=sc.studentid and co.CourseId=sc.CourseId
group by StudentName,score,sc.courseId having sc.CourseId=