在MYSQL的最新版本MYSQL8已經(jīng)支持了排名函數(shù)RANK阳似，DENSE_RANK和ROW_NUMBER挫鸽。但是在5.*版本中還不支持這些函數(shù)，只能自己實(shí)現(xiàn)。實(shí)現(xiàn)方法主要用到了條件判斷語(yǔ)句（CASE WHEN或IF）和添加臨時(shí)變量轨功。
基本知識(shí)：
sql語(yǔ)句中，使用@來(lái)定義一個(gè)變量容达。如：@abc
sql語(yǔ)句中古涧，使用:=來(lái)給變量賦值,：@abc:=123,則變量abc的值為123
sql語(yǔ)句中，if(A,B,C)表示花盐，如果A條件成立羡滑，那么執(zhí)行B菇爪，否則執(zhí)行C，如:@abc := if(2>1,100,200)的結(jié)果是柒昏，abc的值為100凳宙。
一、排名分類：
1.1 區(qū)別RANK职祷，DENSE_RANK和ROW_NUMBER
RANK并列跳躍排名：氏涩，并列即相同的值，相同的值保留重復(fù)名次有梆，遇到下一個(gè)不同值時(shí)是尖，跳躍到總共的排名。
DENSE_RANK并列連續(xù)排序：泥耀，并列即相同的值饺汹，相同的值保留重復(fù)名次，遇到下一個(gè)不同值時(shí)痰催，依然按照連續(xù)數(shù)字排名兜辞。
ROW_NUMBER連續(xù)排名：，即使相同的值夸溶，依舊按照連續(xù)數(shù)字進(jìn)行排名逸吵。
二、數(shù)據(jù)準(zhǔn)備：
創(chuàng)建一張分?jǐn)?shù)表蜘醋，里面有字段：分?jǐn)?shù)score激蹲，課程號(hào)course_id和學(xué)生號(hào)student_id乍惊。執(zhí)行如下SQL語(yǔ)句莫瞬，進(jìn)行導(dǎo)入數(shù)據(jù)吧寺。
create table score(
student_id varchar(10),
course_id varchar(10),
score decimal(18,1)
);
?
insert into score values('01' , '01' , 80);
insert into score values('01' , '02' , 90);
insert into score values('01' , '03' , 99);
insert into score values('02' , '01' , 70);
insert into score values('02' , '02' , 60);
insert into score values('02' , '03' , 80);
insert into score values('03' , '01' , 80);
insert into score values('03' , '02' , 80);
insert into score values('03' , '03' , 80);
insert into score values('04' , '01' , 50);
insert into score values('04' , '02' , 30);
insert into score values('04' , '03' , 20);
insert into score values('05' , '01' , 76);
insert into score values('05' , '02' , 87);
insert into score values('06' , '01' , 31);
insert into score values('06' , '03' , 34);
insert into score values('07' , '02' , 89);
insert into score values('07' , '03' , 98);
insert into score values('08' , '02' , 89);
insert into score values('09' , '02' , 89);
三超凳、不分組排名
3.1 連續(xù)排名：
- 使用
ROW_NUMBER實(shí)現(xiàn)：
>SELECT score,
ROW_NUMBER() OVER (ORDER BY score DESC) ranking
FROM score;
- 使用
變量實(shí)現(xiàn)：
SELECT
s.score,(@cur_rank := @cur_rank + 1) AS Rank
FROM score s,(SELECT @cur_rank := 0) r
ORDER BY s.score DESC
3.2 并列跳躍排名
- 使用
RANK實(shí)現(xiàn)：
SELECT course_id, score,
RANK() OVER(ORDER BY score DESC)
FROM score;
- 使用
變量和IF語(yǔ)句實(shí)現(xiàn)：
SELECT s.score ,
@cur_count := @cur_count + 1,
if(@pre_score = s.score,@cur_rank,@cur_rank := @cur_count) ranking,
@pre_score := s.score
FROM score s,(SELECT @cur_count := 0,@cur_rank:=0,@pre_score := NULL) t
ORDER BY s.score DESC
Result1
- 使用
變量和CASE來(lái)實(shí)現(xiàn)：
SELECT s.score,
@cur_count := @cur_count + 1,
(
case
when @pre_score = s.score then @cur_rank
when @pre_score := s.score then @cur_rank := @cur_count
END
) ranking,
@pre_score := s.score
FROM score s,(SELECT @cur_count := 0,@cur_rank := 0,@pre_score := NULL) r
ORDER BY s.score DESC ;
3.3 并列連續(xù)排名
- 使用
DENSE_RANK實(shí)現(xiàn)：
SELECT course_id, score,
DENSE_RANK() OVER(ORDER BY score DESC) FROM score;
- 使用
變量和IF語(yǔ)句實(shí)現(xiàn)：
SELECT
s.score,
@cur_rank := @cur_rank + 1 ranking
FROM SCORE s,(SELECT @pre_score := NULL ,@cur_rank := 0) r
ORDER BY s.score DESC ;
- 使用
變量和CASE語(yǔ)句實(shí)現(xiàn)：
SELECT
s.score,
case
when @pre_score = score then @cur_rank
when @pre_score := score then @cur_rank := @cur_rank + 1 END ranking
FROM SCORE s,(SELECT @pre_score := NULL ,@cur_rank := 0) r
ORDER BY s.score DESC ;
四诵冒、分組排名
4.1 分組連續(xù)排名
- 使用
ROW_NUMBER實(shí)現(xiàn)：
SELECT
course_id,
score,
ROW_NUMBER() OVER (PARTITION BY course_id ORDER BY score DESC) ranking
FROM score;
- 使用
變量和IF實(shí)現(xiàn)：
SELECT
s.course_id,
s.score,
if(@pre_course_id = s.course_id,@cur_rank := @cur_rank + 1 ,@cur_rank := 1) ranking,
@pre_course_id := s.course_id
FROM score s,(SELECT @cur_rank := 0 ,@pre_course_id := NULL) r
ORDER BY s.course_id,s.score DESC ;
- 使用
變量和CASE實(shí)現(xiàn)：
SELECT
s.course_id,
s.score,
(
case
when @pre_course_id = s.course_id then @cur_rank := @cur_rank + 1
when @pre_course_id := s.course_id then @cur_rank := 1 end
) ranking
FROM score s ,(SELECT @cur_rank := 0 ,@pre_course_id := NULL) r
ORDER BY s.course_id,s.score DESC
Result
4.2 分組并列跳躍排名
- 使用
RANK實(shí)現(xiàn)：
SELECT course_id, score,
RANK() OVER(PARTITION BY course_id ORDER BY score DESC)
FROM score;
- 使用
變量和IF語(yǔ)句實(shí)現(xiàn)：
SELECT s.course_id, s.score,
IF(@pre_course_id = s.course_id,
@rank_counter := @rank_counter + 1,
@rank_counter := 1) temp1,
IF(@pre_course_id = s.course_id,
IF(@pre_score = s.score, @cur_rank, @cur_rank := @rank_counter),
@cur_rank := 1) ranking,
@pre_score := s.score temp2,
@pre_course_id := s.course_id temp3
FROM score s, (SELECT @cur_rank := 0, @pre_course_id := NULL, @pre_score := NULL, @rank_counter := 1)r
ORDER BY s.course_id, s.score DESC;
4.3 分組并列連續(xù)排名
- 使用
DENSE_RANK實(shí)現(xiàn)：
SELECT course_id, score,
DENSE_RANK() OVER(PARTITION BY course_id ORDER BY score DESC)
FROM score;
- 使用
變量和IF語(yǔ)句實(shí)現(xiàn)：
SELECT
s.course_id,
s.score,
IF(@pre_course_id = s.course_id,
IF(@pre_score = s.score, @cur_rank, @cur_rank := @cur_rank + 1),
@cur_rank := 1) ranking,
@pre_score := s.score,
@pre_course_id := s.course_id
FROM score s, (SELECT @cur_rank :=0, @pre_score = NULL, @pre_course_id := NULL) r
ORDER BY course_id, score DESC;
可以將上述的IF條件提取出來(lái)：
SELECT
s.course_id,
s.score,
IF(@pre_score = s.score, @cur_rank, @cur_rank := @cur_rank + 1) temp1,
@pre_score := s.score temp2,
IF(@pre_course_id = s.course_id, @cur_rank, @cur_rank := 1) ranking,
@pre_course_id := s.course_id
FROM score s, (SELECT @cur_rank :=0, @pre_score = NULL, @pre_course_id := NULL) r
ORDER BY course_id, score DESC;
參考：https://blog.csdn.net/u011726005/article/details/94592866
