本文主要提及了我在学习过程中遇到比较好的题目,以及自己的做法,仅供参考
学生表Student
Sno | Sname | Ssex | Sage | Sdept |
95001 | 李敏勇 | 男 | 20 | CS |
95002 | 刘晨 | 女 | 19 | IS |
95003 | 王敏 | 女 | 18 | MA |
95004 | 张立 | 男 | 18 | IS |
课程表Course
Cno | Cname | Cpno | Credit | Teacher |
1 | 数据库 | 5 | 4 | 王芳 |
2 | 数学 | NULL | 2 | 刘新 |
3 | 信息系统 | 1 | 4 | 刘新 |
4 | 操作系统 | 6 | 3 | 高升 |
5 | 数据结构 | 7 | 4 | 宋明 |
6 | 数据处理 | NULL | 2 | 张彬 |
7 | Pascal语言 | 6 | 4 | 李磊 |
学生选修表SC
Sno | Cno | Grade |
95001 | 1 | 92 |
95001 | 2 | 85 |
95001 | 3 | 88 |
95002 | 2 | 90 |
95003 | 2 | 55 |
95004 | 2 | 70 |
查询每个学生的学号、课程号及分数,同时统计每个学生的总分
SELECT
a.Sno AS 学号,
a.Sname AS 姓名,
MAX(CASE WHEN b.Cno = 1 THEN Grade ELSE 0 END) AS '数据库Cno=1',
MAX(CASE WHEN b.Cno = 2 THEN Grade ELSE 0 END) AS '数学Cno=2',
MAX(CASE WHEN b.Cno = 3 THEN Grade ELSE 0 END) AS '信息系统Cno=3',
SUM(Grade) AS 总分
FROM
Student a
LEFT JOIN
SC b ON a.Sno = b.Sno
GROUP BY
a.Sno, a.Sname
查询每个学生的各科分数、最高分、最低分、总分、平均分
SELECT
a.Sno AS 学号,
a.Sname AS 姓名,
MAX(CASE WHEN b.Cno = 1 THEN Grade ELSE 0 END) AS 数据库,
MAX(CASE WHEN b.Cno = 2 THEN Grade ELSE 0 END) AS 数学,
MAX(CASE WHEN b.Cno = 3 THEN Grade ELSE 0 END) AS 信息系统,
MAX(Grade) AS 最高分,
MIN(Grade) AS 最低分,
SUM(Grade) AS 总分,
AVG(Grade) AS 平均分
FROM
Student a
LEFT JOIN
SC b ON a.Sno = b.Sno
GROUP BY
a.Sno, a.Sname
有问题的朋友可以私聊或者在下面评论