mysql 排名

mysql 排名

• • • 1、不分组排名
    • • 1、连续排名
      • 2、跳跃并列排名
      • 3、连续并列排名
    • 2、分组排名
    • • 1、分组连续排名
      • 2、分组并列跳跃排名
      • 3、分组并列连续排名

1、不分组排名

1、连续排名

## mysql 8
SELECTscore,ranking 
FROM(SELECTs.score,ROW_NUMBER () OVER ( ORDER BY score DESC ) ranking FROM(SELECT 0 AS score UNION ALLSELECT 0 AS scoreUNION ALLSELECT 0 AS score UNION ALLSELECT 1 AS score UNION ALLSELECT 2 AS score UNION ALLSELECT 1 AS score ) sORDER BYs.score DESC ) a;## mysql5.7
select score,ranking
from (SELECT s.score,(@cur_rank := @cur_rank + 1) AS rankingFROM (select 0 as score union allselect 0 as score union allselect 0 as score union allselect 1 as scoreunion allselect 2 as scoreunion all select 1 as score )s,(SELECT @cur_rank := 0)  r ORDER BY s.score desc
) a 
;

2、跳跃并列排名

## mysql8
SELECTscore,ranking 
FROM(SELECTs.score,RANK() OVER(ORDER BY score DESC)as rankingFROM(SELECT0 AS score UNION ALLSELECT0 AS score UNION ALLSELECT0 AS score UNION ALLSELECT1 AS score UNION ALLSELECT2 AS score UNION ALLSELECT1 AS score ) s) a;##mysql 5.7
select score,ranking
from (SELECT s.score,@cur_count := @cur_count + 1,if(@pre_score = s.score,@cur_rank,@cur_rank := @cur_count) ranking,@pre_score := s.scoreFROM (select 0 as score union allselect 0 as score union allselect 0 as score union allselect 1 as scoreunion allselect 2 as scoreunion all select 1 as score )s,(SELECT @cur_count := 0,@cur_rank:=0,@pre_score := NULL)  r ORDER BY s.score desc
) a 
;

3、连续并列排名

##mysql 8
SELECTscore,ranking 
FROM(SELECTs.score,
DENSE_RANK() OVER(ORDER BY score DESC)as rankingFROM(SELECT0 AS score UNION ALLSELECT0 AS score UNION ALLSELECT0 AS score UNION ALLSELECT1 AS score UNION ALLSELECT2 AS score UNION ALLSELECT1 AS score ) s) a;##mysql5.7
## if  实现连续并列排名
select score,ranking
from (SELECT s.score,IF(@pre_score = score, @cur_rank , @cur_rank :=@cur_rank +1 ) AS ranking,@pre_score := scoreFROM (select 0 as score union allselect 0 as score union allselect 0 as score union allselect 1 as scoreunion allselect 2 as score)s,(SELECT @pre_score := null ,@cur_rank := 0)  r ORDER BY s.score desc
) a 
;## 连续并列排名  case ...when ... then  遇到零会出现空排名
select score,ranking
from (SELECT s.score,CASEWHEN @pre_score = score THEN  @cur_rankWHEN @pre_score := score THEN @cur_rank :=@cur_rank +1END AS rankingFROM (select 0 as score union allselect 0 as score union allselect 0 as score union allselect 1 as scoreunion allselect 2 as score)s,(SELECT @pre_score := null ,@cur_rank := 0)  r ORDER BY s.score desc
) a 
;

2、分组排名

1、分组连续排名

##mysql 8
select 
cid,score,ranking
from (SELECT s.score,s.cid,ROW_NUMBER() OVER (PARTITION BY s.cid ORDER BY s.score DESC) rankingFROM (select 0 as score , '第一组' as cidunion allselect 0 as score , '第一组' as cidunion allselect 0 as score  , '第二组' as cidunion allselect 1 as score , '第二组' as cidunion allselect 2 as score , '第一组' as cid)s
) a 
;##mysql5.7
select 
cid,score,ranking
from (SELECT s.score,s.cid,IF(@pre_cid = cid, @cur_rank :=@cur_rank +1,@cur_rank :=1) AS ranking,@pre_cid := cidFROM (select 0 as score , '第一组' as cidunion allselect 0 as score , '第一组' as cidunion allselect 0 as score  , '第二组' as cidunion allselect 1 as score , '第二组' as cidunion allselect 2 as score , '第一组' as cid)s,(SELECT @pre_cid := null ,@cur_rank := 0)  r ORDER BY s.cid,s.score desc
) a 
;

2、分组并列跳跃排名

##mysql 8
select 
cid,score,ranking
from (SELECT s.score,s.cid,RANK() OVER(PARTITION BY s.cid ORDER BY s.score DESC) rankingFROM (select 0 as score , '第一组' as cidunion allselect 0 as score , '第一组' as cidunion allselect 0 as score  , '第二组' as cidunion allselect 1 as score , '第二组' as cidunion allselect 2 as score , '第一组' as cid)s
) a 
;##mysql5.7select 
cid,score,ranking
from (
SELECT s.cid, s.score,
IF(@pre_cid = s.cid,@rank_counter := @rank_counter + 1,@rank_counter := 1) temp1,
IF(@pre_cid = s.cid,IF(@pre_score = s.score, @cur_rank, @cur_rank := @rank_counter),@cur_rank := 1) ranking,
@pre_score := s.score temp2,
@pre_cid := s.cid temp3
FROM (	select 0 as score , '第一组' as cidunion allselect 0 as score , '第一组' as cidunion allselect 0 as score  , '第二组' as cidunion allselect 1 as score , '第二组' as cidunion allselect 2 as score , '第一组' as cid) s, (SELECT @cur_rank := 0, @pre_cid := NULL, @pre_score := NULL, @rank_counter := 1)r
ORDER BY s.cid, s.score DESC
) a 
;

3、分组并列连续排名

##mysql8
select 
cid,score,ranking
from (
SELECT s.cid, s.score,
DENSE_RANK() OVER(PARTITION BY s.cid ORDER BY s.score DESC)ranking
FROM (	select 0 as score , '第一组' as cidunion allselect 0 as score , '第一组' as cidunion allselect 0 as score  , '第二组' as cidunion allselect 1 as score , '第二组' as cidunion allselect 2 as score , '第一组' as cid) s
) a 
;##mysql.7
select 
cid,score,ranking
from (
SELECT s.cid, s.score,
IF(@pre_score = s.score, @cur_rank, @cur_rank := @cur_rank + 1) temp1,
@pre_score := s.score temp2,
IF(@pre_cid = s.cid, @cur_rank, @cur_rank := 1) ranking,
@pre_cid := s.cid temp3
FROM (	select 0 as score , '第一组' as cidunion allselect 0 as score , '第一组' as cidunion allselect 0 as score  , '第二组' as cidunion allselect 1 as score , '第二组' as cidunion allselect 2 as score , '第一组' as cid) s, (SELECT @cur_rank :=0, @pre_score = NULL, @pre_cid := NULL) r
ORDER BY cid, score DESC
) a 
;

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！