- 表结构如下图
+-----+------------+-----------+-------+
| 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表的课程id和s2表的课程id对应起来,但是会产生多余的数据(会将不同sid,student_id,score,但是course_id相同的数据都连接一次),所以需要第二个条件进一步筛选我们的理想数据-
按照第二个条件
s1.score >= s2.score将分数进行对比,然后进行连接,连接后的结果就是在同一门课程中,将每一个分数与其他分数(包括自己)进行一一对比,只留下大于自己,或者等于自己的分数.-
到了这里,经过连接后的表中的内容理想的情况会是,
- 100分是最高的,所以几乎其他所有分数都符合
100>=其他分数这个条件,所以100分出现次数最多, - 又比如0分,是最低分,几乎其他所有分数都不符合
0>=其他分数这个条件,所以0分出现的次数应该是最少的, - 至此,我们只要按
group by s1.course_id,s1.score分组,然后count(s2.score)出现次数从多到少排序可以找到每门课程从高到低的分数了.
这里再说一下为什么是
count(s2.score),而不是count(s1.score),因为我们是按s1.score分组的,如果取count(s1.score),得到的结果都会是1 - 100分是最高的,所以几乎其他所有分数都符合
-
但是,理想是丰满的,现实却很骨感,由于相同分数情况的出现,单纯的去统计按照
s1.course_id = s2.course_id and s1.score >= s2.score条件连接表的
s2.score出现次数并不能准确的排列出最高分和最低分,举个例子说明一下:- 比如不同的学生,同一门课程,都是60分,而且这种情况很多,这就会导致我们上面所说的查询方法错误,有可能60出现的次数甚至超过100分,从而导致排序后出现的最高分成了60分.有多少个学生都是同一门课程相同的分数,我们上面所统计的个数就会多几次.
+-----------+-------+-----------------+ | course_id | score | count(s2.score) | +-----------+-------+-----------------+ | 1 | 0 | 1 | | 1 | 59 | 2 | | 1 | 60 | 8 | | 1 | 77 | 5 | | 1 | 99 | 6 | | 1 | 100 | 16 | | 2 | 1 | 1 | | 2 | 20 | 2 | | 2 | 59 | 3 | | 2 | 78 | 4 | | 2 | 99 | 5 | | 2 | 100 | 14 | # 如上,课目1的60分出现次数超过了77分出现的次数,但是明显60是应该排在77之后的. -
所以select语句只能写成这样
select s1.course_id,s1.score,count(distinct s2.score) 要去重!!!
首先通过
group by s1.course_id,s1.score分组,将所有相同课程,相同分数的数据分到了一个组里面,通过count(distinct s2.score)中的distinct把重复出现的相同课程,相同分数的数据去掉!!!得到我们想要的数据
+-----------+-------+--------------------------+ | course_id | score | count(distinct s2.score) | +-----------+-------+--------------------------+ | 1 | 0 | 1 | | 1 | 59 | 2 | | 1 | 60 | 3 | | 1 | 77 | 4 | | 1 | 99 | 5 | | 1 | 100 | 6 | | 2 | 1 | 1 | | 2 | 20 | 2 | | 2 | 59 | 3 | | 2 | 78 | 4 | | 2 | 99 | 5 | | 2 | 100 | 6 |得到上面这种数据,我们就可以很方便的取每门课程前几名,或者取最高,最低分数.
-
如果需要把最高的分数显示为1,第二的分数显示为2,只需要将语句中的
>大于号改成<小于号即可 如果还不能理解的话,建议一步一步加条件查看实际表的数据,来体会每一条条件语句的作用
先看
select *
from score as s1
inner join score as s2
on s1.course_id = s2.course_id ;
再看
select *
from score as s1
inner join score as s2
on s1.course_id = s2.course_id and s1.score >= s2.score;
然后是
select s1.course_id,s1.score,count(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;
最后是
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;
