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把这张中间表和原表自然连接起来，很容易就得出结果了。