话不多说,直接上代码,后面是代码解析
SELECT stu.sname AS 姓名,cou.cname AS 课程名,sco.degree AS 成绩,t3.Avgdegree AS 每平均分,t4.Avgdegree AS 每门课程平均分
FROM Student stu
INNER JOIN Score sco ON stu.sno = sco.sno
INNER JOIN Course cou ON cou.cno = sco.cno
INNER JOIN
(
SELECT t1.sno,t1.sname,G(t2.degree) AS Avgdegree
FROM Student t1
INNER JOIN Score t2 ON t1.sno = t2.sno
GROUP BY t1.sno,t1.sname
) t3 ON stu.sno = t3.sno
INNER JOIN
(
SELECT cno,G(degree) as Avgdegree
FROM score
GROUP BY cno
) t4 ON sco.cno = t4.cno
代码解析:
该问题的关键点在于,怎么用一行代码计算出每的平均分,以及每门课程的平均分
这时需要用到临时表T3、T4
临时表T3,用于计算每平均分
临时表T4,用于计算每门课程平均分
上一篇:sql学生表 成绩表 课程表
下一篇:sql查询每门课程成绩最高的学生