Postgresql 第 23 题

23、查询各科成绩前三名的记录

①、

select  sid, sname, cid1, score1
FROM  student AS  S  INNER JOIN (
select sid1,cid1,score1
from (
SELECT SC1.sid AS sid1,SC1.cid AS cid1,SC1.score AS score1,
	case when SC1.score<SC2.score then 1 else 0 end AS scorenumber
	FROM SC AS SC1 INNER JOIN SC AS SC2
	ON SC1.cid=SC2.cid) AS SC3
	WHERE score1 is not null
	group by SC3.sid1,SC3.cid1,SC3.score1
	having sum(scorenumber)<=2
) AS  SC4
ON S.sid=SC4.sid1
ORDER BY cid1,score1 DESC;

    sid     |         sname          |    cid1    | score1
------------+------------------------+------------+--------
 03         | 孙风                   | 01         |     80
 05         | 周梅                   | 01         |     76
 02         | 钱电                   | 01         |     70
 01         | 赵雷                   | 02         |     90
 07         | 郑竹                   | 02         |     89
 05         | 周梅                   | 02         |     87
 01         | 赵雷                   | 03         |     99
 07         | 郑竹                   | 03         |     98
 03         | 孙风                   | 03         |     80
 02         | 钱电                   | 03         |     80
(10 行记录)

②、网络答案

SELECT *
FROM( 
SELECT Sid, Cid, Score, 
	RANK() OVER (PARTITION BY Cid ORDER BY Score DESC) AS ranking 
FROM SC 
GROUP BY Sid, Cid) AS A 
WHERE A.ranking IN (1, 2, 3);

这个厉害了！学到了学到了！！！
但是大神这个答案group by 与 select 提取的列不一致

③、网络答案修改
1>、因为SC表中sid与cid是组合主键所以删除了group by 去重筛选
2>、因为score可能存在为NULL 的情况所以加了where筛选条件（原数据表中score不存在null值，个人在原表的基础上修改了一条数据使得其score为null）

SELECT *
FROM( 
SELECT Sid, Cid, Score, 
	RANK() OVER (PARTITION BY Cid ORDER BY Score DESC) AS ranking 
FROM SC 
where score is not null
) AS A 
WHERE A.ranking IN (1, 2, 3);

    sid     |         sname          |    cid1    | score1
------------+------------------------+------------+--------
 03         | 孙风                   | 01         |     80
 05         | 周梅                   | 01         |     76
 02         | 钱电                   | 01         |     70
 01         | 赵雷                   | 02         |     90
 07         | 郑竹                   | 02         |     89
 05         | 周梅                   | 02         |     87
 01         | 赵雷                   | 03         |     99
 07         | 郑竹                   | 03         |     98
 03         | 孙风                   | 03         |     80
 02         | 钱电                   | 03         |     80
(10 行记录)

测试：
①

SELECT Sid, Cid, Score
FROM SC 
GROUP BY Sid, Cid，Score
HAVING RANK() OVER (PARTITION BY Cid ORDER BY Score DESC) IN (1,2,3);

②

SELECT Sid, Cid, Score
FROM SC 
WHERE RANK() OVER (PARTITION BY Cid ORDER BY Score DESC) IN (1,2,3)
GROUP BY Sid, Cid，Score;

以上两个程序都是错误的，因为窗口函数只允许在SELECT 中使用