【LeetCode--数据库】分数排名

178. Rank Scores（中等）

题目：编写一个sql语句来实现分数排名，如果两个分数相同，则两个分数排名（Rank）相同,而且下一个名次应该是下一个连续的整数值，也就是说，名次之间不能有间隔。

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

 

解法一：对于每一个分数，从表中找出有多少个大于或等于该分数的不重复分数，然后降序排列

 

select Score,
(select count(distinct Score) from Scores as s2 where s2.Score >= s1.Score) Rank 
from Scores as s1
order by Score DESC;

解法二：使用联结 join，条件是左表的分数小于等于右表的分数时，对右表的分数进行计数（即计算有几个不重复的分数大于自己，计算结果就是rank），然后根据id分组后，再根据分数降序排列

select s1.Score, count(distinct s2.Score) Rank
from Scores as s1 join Scores as s2 on s1.Score <= s2.Score
group by s1.Id 
order by s1.Score DESC;