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
③优化一下,进行排序