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 | +-------+------+
Subscribe to see which companies asked this question.
题目大意:
编写SQL对分数进行排序。如果两个分数相等,其排名应相同。注意在排名相等的分数之后,下一个排名的数值应该连续。换言之,排名之间不应该有“洞”(跳跃)。
# Write your MySQL query statement below
SELECT s2.Score,s1.Rank FROM
#建立子表,将每个分数进行排名,命名为s1.
# 4.00 1
# 3.85 2
# 3.65 3
# 3.50 4
(
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;