- 表結(jié)構(gòu)如下圖
+-----+------------+-----------+-------+
| 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
;
-
首先將分?jǐn)?shù)表score自連接
按照第一個(gè)條件
s1.course_id = s2.course_id把s1表的課程id和s2表的課程id對(duì)應(yīng)起來(lái),但是會(huì)產(chǎn)生多余的數(shù)據(jù)(會(huì)將不同sid,student_id,score,但是course_id相同的數(shù)據(jù)都連接一次),所以需要第二個(gè)條件進(jìn)一步篩選我們的理想數(shù)據(jù)-
按照第二個(gè)條件
s1.score >= s2.score將分?jǐn)?shù)進(jìn)行對(duì)比,然后進(jìn)行連接,連接后的結(jié)果就是在同一門(mén)課程中,將每一個(gè)分?jǐn)?shù)與其他分?jǐn)?shù)(包括自己)進(jìn)行一一對(duì)比,只留下大于自己,或者等于自己的分?jǐn)?shù).-
到了這里,經(jīng)過(guò)連接后的表中的內(nèi)容理想的情況會(huì)是,
- 100分是最高的,所以幾乎其他所有分?jǐn)?shù)都符合
100>=其他分?jǐn)?shù)這個(gè)條件,所以100分出現(xiàn)次數(shù)最多, - 又比如0分,是最低分,幾乎其他所有分?jǐn)?shù)都不符合
0>=其他分?jǐn)?shù)這個(gè)條件,所以0分出現(xiàn)的次數(shù)應(yīng)該是最少的, - 至此,我們只要按
group by s1.course_id,s1.score分組,然后count(s2.score)出現(xiàn)次數(shù)從多到少排序可以找到每門(mén)課程從高到低的分?jǐn)?shù)了.
這里再說(shuō)一下為什么是
count(s2.score),而不是count(s1.score),因?yàn)槲覀兪前磗1.score分組的,如果取count(s1.score),得到的結(jié)果都會(huì)是1 - 100分是最高的,所以幾乎其他所有分?jǐn)?shù)都符合
-
但是,理想是豐滿的,現(xiàn)實(shí)卻很骨感,由于相同分?jǐn)?shù)情況的出現(xiàn),單純的去統(tǒng)計(jì)按照
s1.course_id = s2.course_id and s1.score >= s2.score條件連接表的
s2.score出現(xiàn)次數(shù)并不能準(zhǔn)確的排列出最高分和最低分,舉個(gè)例子說(shuō)明一下:- 比如不同的學(xué)生,同一門(mén)課程,都是60分,而且這種情況很多,這就會(huì)導(dǎo)致我們上面所說(shuō)的查詢方法錯(cuò)誤,有可能60出現(xiàn)的次數(shù)甚至超過(guò)100分,從而導(dǎo)致排序后出現(xiàn)的最高分成了60分.有多少個(gè)學(xué)生都是同一門(mén)課程相同的分?jǐn)?shù),我們上面所統(tǒng)計(jì)的個(gè)數(shù)就會(huì)多幾次.
+-----------+-------+-----------------+ | 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分出現(xiàn)次數(shù)超過(guò)了77分出現(xiàn)的次數(shù),但是明顯60是應(yīng)該排在77之后的. -
所以select語(yǔ)句只能寫(xiě)成這樣
select s1.course_id,s1.score,count(distinct s2.score) 要去重!!!
首先通過(guò)
group by s1.course_id,s1.score分組,將所有相同課程,相同分?jǐn)?shù)的數(shù)據(jù)分到了一個(gè)組里面,通過(guò)count(distinct s2.score)中的distinct把重復(fù)出現(xiàn)的相同課程,相同分?jǐn)?shù)的數(shù)據(jù)去掉!!!得到我們想要的數(shù)據(jù)
+-----------+-------+--------------------------+ | 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 |得到上面這種數(shù)據(jù),我們就可以很方便的取每門(mén)課程前幾名,或者取最高,最低分?jǐn)?shù).
-
如果需要把最高的分?jǐn)?shù)顯示為1,第二的分?jǐn)?shù)顯示為2,只需要將語(yǔ)句中的
>大于號(hào)改成<小于號(hào)即可 如果還不能理解的話,建議一步一步加條件查看實(shí)際表的數(shù)據(jù),來(lái)體會(huì)每一條條件語(yǔ)句的作用
先看
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;
