Leetcode 解题记录【数据库篇】178. 分数排名

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    |
+-------+------+

分析

此题如果使用Oracle数据库，可以使用dense_rank()分析函数做，相对较简单。

elect s.Score,dense_rank() over (order by score desc) as Rank from Scores s;

考虑到去Oracle化，打算用MySQL解决此问题。用MySQL解决此问题也有很多种方法，这里使用变量和相关子查询解决。

首先在免费在线SQL数据库SQL Fiddle中准备测试表以及数据。

create table `Scores` (
  `Id` int(2) NOT NULL auto_increment,
  `Score` double(5,2) NOT NULL,
  primary key (`Id`)
) engine=InnoDB;
insert into `Scores` (`Id`, `Score`) values
(1, 3.50),
(2, 3.65),
(3, 4.00),
(4, 3.85),
(5, 4.00),
(6, 3.65);

变量

如果不考虑分数相同，则比较简单，只需定义一个变量 c_rank记录名次，然后按分许降序排序，每行将此变量加1即可。

set @c_rank := 0;
select Score,
@c_rank := @c_rank + 1 as `Rank` 
from Scores order by Score desc;

结果如下

| Score | Rank |
+-------+------+
| 4.00  | 1    |
| 4.00  | 2   |
| 3.85  | 3    |
| 3.65  | 4    |
| 3.65  | 5    |
| 3.50  | 6    |
+-------+------+

如果要考虑相同分数，则需要另外定义一个变量p_score记录上一行分数，由于第一行没有上一行所以p_score初始化为NULL。然后在查询每一行数据时，要将当前分数和p_score的值进行比较，如果相同则排名为c_rank，p_score不变。如果不同，则c_rank加1，p_score更新为当前行的分数。

set @p_score := NULL, @c_rank := 0;
select Score,
case 
when @p_score = Score then @c_rank
when @p_score := Score then @c_rank := @c_rank + 1
end as `Rank`
from Scores order by Score desc

运行结果如下

+-------+------+
| Score | Rank |
+-------+------+
| 4.00  | 1    |
| 4.00  | 1    |
| 3.85  | 2    |
| 3.65  | 3    |
| 3.65  | 3    |
| 3.50  | 4    |
+-------+------+

但现实生活中却不是像题意这样连续排序。比如考试排名，如果有两个第二名的话，则没有第三名。为了实现这种排序，可参考如下SQL语句。

set @p_score := NULL, @i_rank := 0, @c_rank := 0;
select Score,
@c_rank := if(Score = @p_score, @c_rank, @i_rank) as `Rank`,
@i_rank := @i_rank + 1,
@p_score := Score
from Scores order by Score desc

相关子查询

首先还是一样，需要将数据按照分数降序排序。
现在解决怎样求出每行的排名，可以这样考虑每个分数的排名即为所有大于自己的不同分数的个数加1。这样不仅可以解决相同分数排名问题，还可以使排名连续。

select Score, 
(
  select count(distinct(Score)) from Scores p where p.Score > m.Score  
) + 1 as `Rank`
from Scores m order by m.Score desc;

运行结果如下

+-------+------+
| Score | Rank |
+-------+------+
| 4.00  | 1    |
| 4.00  | 1    |
| 3.85  | 2    |
| 3.65  | 3    |
| 3.65  | 3    |
| 3.50  | 4    |
+-------+------+