表结构如下图
+-----+------------+-----------+-------+
| sid | student_id | course_id | score |
+-----+------------+-----------+-------+
| 1 | 1 | 1 | 60 |
| 2 | 1 | 2 | 59 |
| 3 | 2 | 2 | 99 |
| 5 | 3 | 1 | 77 |
| 6 | 3 | 2 | 78 |
| 7 | 4 | 1 | 59 |
| 8 | 5 | 2 | 20 |
| 9 | 6 | 1 | 99 |
| 10 | 6 | 2 | 100 |
| 11 | 7 | 1 | 0 |
| 12 | 7 | 2 | 1 |
| 13 | 8 | 1 | 100 |
| 14 | 9 | 2 | 100 |
| 15 | 9 | 3 | 50 |
| 16 | 9 | 1 | 60 |
排名方法如下
select s1.course_id,s1.score,count(distinct s2.score)
from score as s1
inner join score as s2
on s1.course_id = s2.course_id and s1.score >= s2.score
group by s1.course_id,s1.score
;
首先将分数表score自连接
按照第一个条件 s1.course_id = s2.course_id把s1表