Postgresql 第15、16题

15、检索” 01 "课程分数小于 60，按分数降序排列的学生信息

select S.sid,S.sname,S.sage,SC1.score
from student AS S  inner join
(
select sid ,score
from SC
where cid='01'
AND score<60
) AS  SC1
ON  S.sid=SC1.sid
order by SC1.score  DESC;

    sid     |         sname          |    sage    | score
------------+------------------------+------------+-------
 04         | 李云                   | 1990-12-06 |    50
 06         | 吴兰                   | 1992-01-01 |    31
(2 行记录)

16、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

①、

select distinct S.* ,SC1.scoreavg AS 平均成绩
from student AS S inner join SC
ON s.sid=SC.sid
inner join (select sid, AVG(score)  AS scoreavg
from SC
group by sid
) AS SC1
ON SC.sid=SC1.sid
ORDER BY 平均成绩  DESC;

    sid     |         sname          |    sage    | ssex  |      平均成绩
------------+------------------------+------------+-------+---------------------
 01         | 赵雷                   | 1990-01-01 | 男    | 94.5000000000000000
 07         | 郑竹                   | 1989-01-01 | 女    | 93.5000000000000000
 05         | 周梅                   | 1991-12-01 | 女    | 81.5000000000000000
 03         | 孙风                   | 1990-12-20 | 男    | 80.0000000000000000
 02         | 钱电                   | 1990-12-21 | 男    | 70.0000000000000000
 04         | 李云                   | 1990-12-06 | 男    | 33.3333333333333333
 06         | 吴兰                   | 1992-01-01 | 女    | 32.5000000000000000
(7 行记录)

②、网络答案：

SELECT S.Sid, B.平均成绩, 
	SUM(CASE WHEN Cid = '01' 
		THEN Score ELSE NULL END) AS 成绩01, 
	SUM(CASE WHEN Cid = '02' 
		THEN Score ELSE NULL END) AS 成绩02, 
	SUM(CASE WHEN Cid = '03' 
		THEN Score ELSE NULL END) AS 成绩03 
FROM SC INNER JOIN (
SELECT Sid, AVG(score) AS 平均成绩 
FROM SC 
GROUP BY Sid) AS B 
ON B.Sid = SC.Sid 
RIGHT OUTER JOIN Student AS S 
ON S.Sid = SC.Sid 
GROUP BY S.Sid, B.平均成绩 
ORDER BY B.平均成绩 DESC NULLS LAST;

    sid     |      平均成绩       | 成绩01 | 成绩02 | 成绩03
------------+---------------------+--------+--------+--------
 01         | 94.5000000000000000 |        |     90 |     99
 07         | 93.5000000000000000 |        |     89 |     98
 05         | 81.5000000000000000 |     76 |     87 |
 03         | 80.0000000000000000 |     80 |     80 |     80
 02         | 70.0000000000000000 |     70 |     60 |     80
 04         | 33.3333333333333333 |     50 |     30 |     20
 06         | 32.5000000000000000 |     31 |        |     34
 09         |                     |        |        |
 10         |                     |        |        |
 11         |                     |        |        |
 12         |                     |        |        |
 13         |                     |        |        |
(12 行记录)

大神程序中SUM与GROUP BY相对应目的是将课程1、2、3 以三列分别表示（我理解的是
这样，有其他大神指教吗？）其中技巧还需慢慢体会，总感觉这个程序很帅！

题目的意思是所有学生的所有成绩？！所以没有成绩的学生也需要列举出来