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
题解
1 2 3 4 5 6
# Write your MySQL query statement below SELECT s1.Score,COUNT(DISTINCT s2.Score) Rank FROM Scores s1,Scores s2 WHERE s1.Score<=s2.Score GROUP BY s1.Id ORDER BY Rank;
注意
GROUP
BY必须存在，不然只要一条结果，即得分最低的那一条。对id进行分组，才能在每组(即每个人)都给出排名
其他解法
1 2 3
/* Write your PL/SQL query statement below */ select Score,dense_rank() over(order by Score desc) as Rank from Scores;