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