1、不分组排名
1、连续排名
SELECT
score, ranking
FROM
(
SELECT
s. score,
ROW_NUMBER ( ) OVER ( ORDER BY score DESC ) ranking
FROM
(
SELECT 0 AS score
UNION ALL
SELECT 0 AS score
UNION ALL
SELECT 0 AS score
UNION ALL
SELECT 1 AS score
UNION ALL
SELECT 2 AS score
UNION ALL
SELECT 1 AS score
) s
ORDER BY
s. score DESC
) a;
select score, ranking
from (
SELECT
s. score,
( @cur_rank := @cur_rank + 1 ) AS ranking
FROM (
select 0 as score
union all
select 0 as score
union all
select 0 as score
union all
select 1 as score
union all
select 2 as score
union all
select 1 as score
) s, ( SELECT @cur_rank := 0 ) r
ORDER BY s. score desc
) a
;
2、跳跃并列排名
SELECT
score,
ranking
FROM
(
SELECT
s. score,
RANK( ) OVER ( ORDER BY score DESC ) as ranking
FROM
(
SELECT
0 AS score UNION ALL
SELECT
0 AS score UNION ALL
SELECT
0 AS score UNION ALL
SELECT
1 AS score UNION ALL
SELECT
2 AS score UNION ALL
SELECT
1 AS score
) s
) a;
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. score
FROM (
select 0 as score
union all
select 0 as score
union all
select 0 as score
union all
select 1 as score
union all
select 2 as score
union all
select 1 as score
) s, ( SELECT @cur_count := 0 , @cur_rank := 0 , @pre_score := NULL ) r
ORDER BY s. score desc
) a
;
3、连续并列排名
SELECT
score,
ranking
FROM
(
SELECT
s. score,
DENSE_RANK( ) OVER ( ORDER BY score DESC ) as ranking
FROM
(
SELECT
0 AS score UNION ALL
SELECT
0 AS score UNION ALL
SELECT
0 AS score UNION ALL
SELECT
1 AS score UNION ALL
SELECT
2 AS score UNION ALL
SELECT
1 AS score
) s
) a;
select score, ranking
from (
SELECT
s. score,
IF ( @pre_score = score, @cur_rank , @cur_rank := @cur_rank + 1 ) AS ranking,
@pre_score := score
FROM (
select 0 as score
union all
select 0 as score
union all
select 0 as score
union all
select 1 as score
union all
select 2 as score
) s, ( SELECT @pre_score := null , @cur_rank := 0 ) r
ORDER BY s. score desc
) a
;
select score, ranking
from (
SELECT
s. score,
CASE
WHEN @pre_score = score THEN @cur_rank
WHEN @pre_score := score THEN @cur_rank := @cur_rank + 1
END AS ranking
FROM (
select 0 as score
union all
select 0 as score
union all
select 0 as score
union all
select 1 as score
union all
select 2 as score
) s, ( SELECT @pre_score := null , @cur_rank := 0 ) r
ORDER BY s. score desc
) a
;
2、分组排名
1、分组连续排名
select
cid, score, ranking
from (
SELECT
s. score,
s. cid,
ROW_NUMBER( ) OVER ( PARTITION BY s. cid ORDER BY s. score DESC ) ranking
FROM (
select 0 as score , '第一组' as cid
union all
select 0 as score , '第一组' as cid
union all
select 0 as score , '第二组' as cid
union all
select 1 as score , '第二组' as cid
union all
select 2 as score , '第一组' as cid
) s
) a
;
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 := cid
FROM (
select 0 as score , '第一组' as cid
union all
select 0 as score , '第一组' as cid
union all
select 0 as score , '第二组' as cid
union all
select 1 as score , '第二组' as cid
union all
select 2 as score , '第一组' as cid
) s, ( SELECT @pre_cid := null , @cur_rank := 0 ) r
ORDER BY s. cid, s. score desc
) a
;
2、分组并列跳跃排名
select
cid, score, ranking
from (
SELECT
s. score,
s. cid,
RANK( ) OVER ( PARTITION BY s. cid ORDER BY s. score DESC ) ranking
FROM (
select 0 as score , '第一组' as cid
union all
select 0 as score , '第一组' as cid
union all
select 0 as score , '第二组' as cid
union all
select 1 as score , '第二组' as cid
union all
select 2 as score , '第一组' as cid
) s
) a
;
select
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 cid
union all
select 0 as score , '第一组' as cid
union all
select 0 as score , '第二组' as cid
union all
select 1 as score , '第二组' as cid
union all
select 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、分组并列连续排名
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 cid
union all
select 0 as score , '第一组' as cid
union all
select 0 as score , '第二组' as cid
union all
select 1 as score , '第二组' as cid
union all
select 2 as score , '第一组' as cid) s
) a
;
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 cid
union all
select 0 as score , '第一组' as cid
union all
select 0 as score , '第二组' as cid
union all
select 1 as score , '第二组' as cid
union all
select 2 as score , '第一组' as cid) s, ( SELECT @cur_rank := 0 , @pre_score = NULL , @pre_cid := NULL ) r
ORDER BY cid, score DESC
) a
;