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

方法一：Oracle Rank

SELECT Score, DENSE_Rank() OVER(ORDER BY Score DESC) AS Rank 
FROM Scores
ORDER BY Rank

tips: differences between row_number、rank、DENSE_Rank

1.ROW_NUMBER()排序并为查询出的每一行生成一个序号，用over子句选择排序的列。排序序列连续无间断。原理是根据over中的order by子句排序后，根据该排序后的序列生成序号。如果sql子句中其他部分也要使用order by子句，需要与over中的order by子句保持一致，否则生成的序列可能不连续。

select ROW_NUMBER() over(order by score desc) as rank, * from Scores

2.RANK()函数用于返回结果集的分区内每行的排名，当存在相同成绩的学生时，ROW_NUMBER()会依次进行排序，他们序号不相同，而Rank()排序序号相同的。出现不同值时，排序会间断。

3.DENSE_RANK() 排序，排序序号不间断。dense_rank函数在生成序号时是连续的，而rank函数生成的序号有可能不连续。dense_rank函数出现相同排名时，将不跳过相同排名号，rank值紧接上一次的rank值。在各个分组内，rank()是跳跃排序，有两个第一名时接下来就是第四名，dense_rank()是连续排序，有两个第一名时仍然跟着第二名。将上面的Sql语句改由dense_rank函数来实现。

汇总：

RANK()                        发生不持续的编号 例如数据值 1,2,2,3 发生的编号将是1,2,2,4

DENSE_RANK()        发生持续的编号 例如数据值 1,2,2,3 发生的编号将是1,2,2,3

ROW_NUMBER()     发生持续的编号(不重复) 例如数据值 1,2,2,3 发生的编号将是1,2,3,4
--------------------- 
原文链接：https://blog.csdn.net/s630730701/article/details/51902762

方法二：SQL  count计算score排名

SELECT
  Score,
  (SELECT count(distinct Score) FROM Scores WHERE Score >= s.Score) Rank
FROM Scores s
ORDER BY Score desc

方法三：我看不太懂，求大神解释

SELECT
  Score,
  @rank := @rank + (@prev <> (@prev := Score)) Rank
FROM
  Scores,
  (SELECT @rank := 0, @prev := -1) init
ORDER BY Score desc

Same as the previous one, but faster because I have a subquery that "uniquifies" the scores first. 
Not entirely sure why it's faster, I'm guessing MySQL makes tmp a temporary table and uses it for every outer Score.

SELECT
  Score,
  (SELECT count(*) FROM (SELECT distinct Score s FROM Scores) tmp WHERE s >= Score) Rank
FROM Scores
ORDER BY Score desc

方法四：

SELECT s.Score, count(distinct t.score) Rank
FROM Scores s JOIN Scores t ON s.Score <= t.score
GROUP BY s.Id
ORDER BY s.Score desc

大佬的世界我不懂。。。

Ref:https://leetcode.com/problems/rank-scores/discuss/53094/Simple-Short-Fast