[LeetCode][SQL]Rank Scores

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

https://leetcode.com/problems/rank-scores/

难！不应该是hard吗。

可能用变量会好做很多。

1 select C.Score, ranking.rank from
2     (select A.Score, count(*) as rank from 
3         ( select distinct Score from Scores ) A, 
4         ( select distinct Score from Scores ) B 
5     where A.Score <= B.Score 
6     group by A.Score desc) as ranking,
7     Scores C
8 where C.Score = ranking.Score
9 order by C.Score desc

参考了这个 https://leetcode.com/discuss/26113/accepted-solution-with-subqueries-and-group-by

里层的select是为了找出排名的表。

1 select A.Score, count(*) as rank from 
2     ( select distinct Score from Scores ) A, 
3     ( select distinct Score from Scores ) B 
4 where A.Score <= B.Score 
5 group by A.Score desc

结果：

比如3.65，它小于等于4, 3.85和3.65，所以count(*)是3.

这一步很巧妙。

然后就简单了，用Score把这张中间表和原表自然连接起来，很容易就得出结果了。