练习18-20. 窗口函数、distinct聚合、子查询

18. 查询各科成绩前三名的记录（分数相同时只占一个名次）

第一步：各科成绩排名

SELECT 
*,
dense_rank() over (PARTITION BY cid ORDER BY score DESC) AS score_rk
FROM sc;

第二步：获取各科的前三名，score_rk <= 3 即可

SELECT *  FROM(
SELECT 
*,
dense_rank() over (PARTITION BY cid ORDER BY score DESC) AS score_rk
FROM sc) a
WHERE score_rk <= 3;

注意：不可以写成下面这种形式，因为窗口函数无法直接放到where条件中，只能向上面那里将查询结果作为子表再次查询

SELECT 
*,
dense_rank() over (PARTITION BY cid ORDER BY score DESC) AS score_rk
FROM sc
WHERE score_rk <= 3;

19. 查询每门课程选修的学生人数

统计的时候注意使用distinct去重，因为一个学生在一门课下可能有多个成绩（考试过多次）

SELECT
cid,
COUNT(DISTINCT sid)
FROM sc
GROUP BY cid

20. 查询出只选修两门课程的学生学号和姓名

第一步：锁定表，成绩表，学生表

第二步：统计每个学生选修的课程总数，并筛选出课程总数等于2的学生id

SELECT 
sid
FROM sc
GROUP BY sid
HAVING COUNT(*) = 2;

第三步：关联学生表，获取学生姓名

SELECT sid,sname
FROM student
WHERE sid IN (
SELECT 
sid
FROM sc
GROUP BY sid
HAVING COUNT(*) = 2);