目录
题目:分数排名
编写一个 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 |
+-------+------+
解1:使用rownum
时间 复杂度较高,使用最常用的函数。
均为Oracle语法
SELECT
d.score AS score,
d.r AS RANK
FROM
(
SELECT
score,
rownum AS r
FROM
( SELECT score, COUNT ( * ) AS C FROM scores A GROUP BY A.score ORDER BY A.score DESC )) d,
scores e
WHERE
e.score = d.score
ORDER BY
d.r
解2:使用开窗函数
Oracle语法
select Score, dense_rank() over (order by Score desc) as Rank
from Scores;
msyql语法
开窗函数在msyql8.0以后支持
与Oracle区别是需要将rank转移,在msyql中,rank是内置函数。
select Score, dense_rank() over (order by Score desc) as 'Rank'
from Scores;