mysql 之 分数排名
知识点:以下主要是针对于窗口函数排序(row_number,rank,dense_rank)进行解答,不同的排序函数,返回的结果也不一样,重点看结果中标红部分
id | score |
---|---|
1 | 30 |
2 | 25 |
3 | 79 |
4 | 25 |
题目:对上表中的分数进行排名
sql
解法一:
select score,dense_rank() over(partition by d order by score desc) as rn
from
(
select 1 as d, score from Scores
)t;
返回值(dense_rank)
score | rn |
---|---|
25 | 1 |
25 | 1 |
30 | 2 |
79 | 3 |
解法二:
select score,rank() over(partition by d order by score desc) as rn
from
(
select 1 as d, score from Scores
)t;
返回值(rank)
score | rn |
---|---|
25 | 1 |
25 | 1 |
30 | 3 |
79 | 4 |
解法三:
select score,row_number() over(partition by d order by score desc) as rn
from
(
select 1 as d, score from Scores
)t;
返回值(row_number)
score | rn |
---|---|
25 | 1 |
25 | 2 |
30 | 3 |
79 | 4 |