一、178分数排名
编写一个 SQL 查询来实现分数排名。
如果两个分数相同,则两个分数排名(Rank)相同。请注意,平分后的下一个名次应该是下一个连续的整数值。换句话说,名次之间不应该有“间隔”。
题目链接
Id | Score |
---|---|
1 | 3.50 |
2 | 3.65 |
3 | 4.00 |
4 | 3.85 |
5 | 4.00 |
6 | 3.65 |
例如,根据上述给定的 Scores 表,你的查询应该返回(按分数从高到低排列):
Score | Rank |
---|---|
4.00 | 1 |
4.00 | 1 |
3.85 | 2 |
3.65 | 3 |
3.65 | 3 |
3.50 | 4 |
二、解题思路
看到排名就要想到的三个窗口函数
rank() 间断不连续
dense_rank() 连续
row_number() 行号
1.使用窗口函数
根据题目要求名次是一个连续的整数值,所以使用dense_rank() 即可。
代码如下(示例):
select
score
,dense_rank() over(order by score desc) as "Rank"
from scores
2.使用子查询
如果想不起来窗口函数还可以使用子查询
思路:根据题意拆分成两部分
- 降序排列的分数
select
score
from scores
order by score desc
- 分数对应的排名
如何获得分数对应的排名呢?假设分数为S,分数集合为H
因为排名是连续的,所以S的名次应该是H中大于等于S的分数去重计数的结果(去重后的分数的个数)
# 大于分数S的集合H1
select score from scores where score >= S;
# 对H1去重计数
select count(distinct score) from scores where score >= S;
- 将1、2结合
#每次从a表中取一个分数都与b表进行比较,b表中大于a的去重的分数的个数就是a的排名
select
a.score
,(select count(distinct b.score) from scores as b where b.score >= a.score) as "Rank"
from scores as a
order by a.score desc
3. 使用内部变量+join(不常用)
代码示例:
# 获得每个不同的分数的名次
select
t1.score
,@rank := @rank + 1 as "rank"
from
(select distinct score as score from scores order by score desc) t1
(select @rank := 0) t2
# 主表与上述的中间表join求得所有分数的排名
select
s.score
,t.rank as "Rank"
from scores as s
inner join (
select
t1.socre
,@rank := @rank + 1 as "rank"
from (select distinct score as score from scores order by score desc) as t1
(select @rank := 0) as t2
) as t
on s.score = t.score
order by s.score desc
总结
本题主要考察排序的窗口函数