标签:窗口函数(rank,dense_rank,roll_number )
2022年12月19日
题目
表: Scores
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| score | decimal |
+-------------+---------+
Id是该表的主键。
该表的每一行都包含了一场比赛的分数。Score是一个有两位小数点的浮点值。
编写 SQL 查询对分数进行排序。排名按以下规则计算:
- 分数应按从高到低排列。
- 如果两个分数相等,那么两个分数的排名应该相同。
- 在排名相同的分数后,排名数应该是下一个连续的整数。换句话说,排名之间不应该有空缺的数字。
按 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 |
+-------+------+
方法一
思路:使用子查询,得出高于或等于自己的成绩,然后去重,将重复的数据去掉,再用count
函数进行计算,比如班上有两个90分的并列第一,去重后集合内就只有1个元素,就作为自己的排名
select
a.score,
(select count(distinct b.score) from Scores b where b.score >= a.score) as 'rank'
from
Scores a
order by
a.score desc
方法二
思路:使用窗口函数
select
score,
dense_rank() over (order by score desc) as 'rank'
from
Scores
窗口函数的格式
<窗口函数> over (partition by <用于分组的列名> order by <用于排序的列名>)
group by分组汇总后改变了表的行数,一行只有一个类别。而partiition by和rank函数不会减少原表中的行数