Database LeetCode Rank Scores

题目：

Write a SQL query to rank scores. If there is a tie between two scores, both should have the same ranking. Note that after a tie, the next ranking number should be the next consecutive integer value. In other words, there should be no "holes" between ranks.

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

For example, given the above Scores table, your query should generate the following report (order by highest score)：

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

题意：

给定一张表示成绩的表格，要求将这张表格中的成绩进行排名，然后输出表格中的各个分数的排名，并且按照从大到小来排列。

题解：

此题需要先对表格中的各个分数进行排序操作，并且得到各个分数的一个排名。这里用一种非常巧妙地方法，那就是用两个表来对比比较，同时得到相关的排名。

select * from (select distinct Score from Scores) as s1,(select distinct Score from Scores) as s2 where s1.Score <= s2.Score;

此语句将得到：

+-------+-------+
| Score | Score |
+-------+-------+
|   3.5 |   3.5 |
|   3.5 |  3.65 |
|  3.65 |  3.65 |
|   3.5 |     4 |
|  3.65 |     4 |
|     4 |     4 |
|  3.85 |     4 |
|   3.5 |  3.85 |
|  3.65 |  3.85 |
|  3.85 |  3.85 |
+-------+-------+

可以看到其实上述语句的作用于内连接的作用一样，也可以写成：

select * from (select distinct Score from Scores) as s1 inner join (select distinct Score from Scores) as s2 on s1.Score <= s2.Score;

如果取左边那一行为限制，改为：

select * from (select distinct Score from Scores) as s1 join (select distinct Score from Scores) as s2 on s1.Score <= s2.Score group by s1.Score;

那么我们可以看到其实是在统计各个分数的排名大小情况，也就是说，比如4，只有4 <= 4。所以，在统计的时候，我们可以发现在左边的那一列中，4出现的次数就为1，而最小的那个出现的次数为4，其实也是它的排名，如果用一个升序排列，就可以直接把各个分数的排名都统计出来。然后再将这张临时表格和原来的分数表格做一个连接，取得我需要的结果。

真正的代码如下：

select s2.Score,s1.Rank from (select s1.Score,count(*) as Rank from (select distinct Score from Scores) as s1,(select distinct Score from Scores) as s2 where s1.Score <= s2.Score group by s1.Score) s1,Scores s2 where s2.Score = s1.Score order by s2.Score desc;