SQL整理（2）查询每门课程成绩最好的前两名（不考虑成绩相同的情况）

SELECT 
sc.c_id,
sc.s_score 
FROM score sc WHERE (
SELECT COUNT(1) FROM score WHERE sc.c_id = score.c_id AND sc.s_score<score.s_score
)<2 ORDER BY sc.c_id ASC,sc.s_score DESC;

分析：

①使用子查询查询：大于当前行成绩的记录

SELECT 
sc.s_id,
sc.c_id,
sc.s_score ,
(
SELECT COUNT(1) FROM score WHERE sc.c_id = score.c_id AND sc.s_score<score.s_score
) 
FROM score sc

②可以根据子查询，查找前两名

SELECT 
sc.c_id,
sc.s_score 
FROM score sc WHERE (
SELECT COUNT(1) FROM score WHERE sc.c_id = score.c_id AND sc.s_score<score.s_score
)<2 

③优化一下，进行排序