分数排名
表:
Scores
+-------------+---------+ | Column Name | Type | +-------------+---------+ | id | int | | score | decimal | +-------------+---------+ 在 SQL 中,id 是该表的主键。 该表的每一行都包含了一场比赛的分数。Score 是一个有两位小数点的浮点值。查询并对分数进行排序。排名按以下规则计算:
- 分数应按从高到低排列。
- 如果两个分数相等,那么两个分数的排名应该相同。
- 在排名相同的分数后,排名数应该是下一个连续的整数。换句话说,排名之间不应该有空缺的数字。
按
score
降序返回结果表。查询结果格式如下所示。
示例 1:
输入: Scores 表: +----+-------+ | id | score | +----+-------+ | 1 | 3.50 | | 2 | 3.65 | | 3 | 4.00 | | 4 | 3.85 | | 5 | 4.00 | | 6 | 3.65 | +----+-------+ 输出: +-------+------+ | score | rank | +-------+------+ | 4.00 | 1 | | 4.00 | 1 | | 3.85 | 2 | | 3.65 | 3 | | 3.65 | 3 | | 3.50 | 4 | +-------+------+
方法一 窗口函数 DENSE_RANK (MySQL 8.0 新特性)
select
s.score,
DENSE_RANK() over (
order by
s.score desc
) as 'rank'
from Scores s;
方法二 相关子查询
select s1.score,
(select count(distinct s2.score)
from Scores s2
where s2.score >= s1.score
) as 'rank'
from Scores s1
order by s1.score desc;
方法三 自连接
select s1.score, count(distinct s2.score) as 'rank'
from Scores s1 join Scores s2
on s1.score <= s2.score
group by
s1.id, s1.score
order by
s1.score desc;
运行结果