SQL: 求每组中排名前N的数据

最近跟朋友讨论的一个问题：
使用SQL求每组中排名前N的行，如并列则输出全部并列值?

就拿SC表来说
SNO为学号 CNO为课程号 SCORE为分数
求每门课排名前N的数据（可能有并列情况）

创建表

CREATE TABLE SC(
    SNO INT NOT NULL,
    CNO VARCHAR(2) NOT NULL,
    SCORE INT NOT NULL,
    PRIMARY KEY (SNO,CNO)
);

插入数据

INSERT INTO SC (SNO,CNO,SCORE) VALUES
(1,'a',3),
(2,'a',5),
(3,'b',2),
(4,'b',4),
(5,'a',4),
(6,'b',1),
(7,'a',7),
(8,'b',7),
(9,'a',7);

答案（朋友想出来的?）

SELECT A.SNO,A.CNO,A.SCORE 
FROM SC AS A 
    JOIN (SELECT * FROM SC GROUP BY CNO,SCORE) AS B
        ON A.CNO=B.CNO AND A.SCORE<=B.SCORE
GROUP BY A.SNO
HAVING COUNT(*)<=2
ORDER BY A.CNO,A.SCORE DESC;

1. 首先将所有的SCORE去重：SELECT * FROM SC GROUP BY CNO,SCORE，得到
   

2. 将完整的SC表A与去重后SC表B进行JOIN，条件是A.CNO=B.CNO AND A.SCORE<=B.SCORE，返回A表的学科=B表的学科并且A表的成绩小于等于B表的成绩的影响行数，得到

3. 对JOIN后的表进行GROUP：GROUP BY A.SNO HAVING COUNT(*)<=2，小于等于2，其中有一行是自己与自己JOIN，那么剩下一行就是B.SCORE>A.SCORE(因为我们在第一步对B表中同一课程的所有分数进行去重了)。这样的行数小于等于2说明该门课程大于该同学分数的最多只有一人**（<=2只会取到1或2，取到1那么这一行就是该同学自己JOIN自己，则其成绩排名第一，取到2那么该同学的成绩就排第二）**
   

4. 最后再按CNO和SCORE排序，得到