[LeetCode] 178. 分数排名
题目：编写一个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;