LeetCode178. Rank Scores - 分数排名 ＜Medium＞

编写一个 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    |
+-------+------+

重要提示：对于 MySQL 解决方案，如果要转义用作列名的保留字，可以在关键字之前和之后使用撇号。例如 `Rank`

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.
For example, given the above Scores table, your query should generate the following report (order by highest score):
Important Note: For MySQL solutions, to escape reserved words used as column names, you can use an apostrophe before and after the keyword. For example `Rank`.

分析题意：查询Score、Rank，按分数降序 且 Rank在Score不同时自动递增；

有如下思路：

1、两个Score表a、b，左表a 左连接 右表b(相同表a、b左连接等同于内交；此外，Mysql中 join == inner join == cross join 详情点击 ，此时相同表内交，left join效果也一样)，右表分数>=左表，分组按分数降序排列；

2、对于每一个分数，使用临时表，找出表中比该分数值大的个数，按分数降序排列；

# Method 1
SELECT a.Score, COUNT(DISTINCT b.Score) `Rank`
FROM Scores a JOIN Scores b ON a.Score <= b.Score
GROUP BY a.Id 
ORDER BY a.Score DESC;

# Method 2
select a.Score,(select count(distinct Score) from Scores where Score >= a.Score) as `Rank` 
from Scores a 
order by Score desc