题目:
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 |
+-------+------+
题意:
给定一张表示成绩的表格,要求将这张表格中的成绩进行排名,然后输出表格中的各个分数的排名,并且按照从大到小来排列。
题解:
此题需要先对表格中的各个分数进行排序操作,并且得到各个分数的一个排名。这里用一种非常巧妙地方法,那就是用两个表来对比比较,同时得到相关的排名。
select * from (select distinct Score from Scores) as s1,(select distinct Score from Scores) as s2 where s1.Score <= s2.Score;
此语句将得到:
+-------+-------+
| Score | Score |
+-------+-------+
| 3.5 | 3.5 |
| 3.5 | 3.65 |
| 3.65 | 3.65 |
| 3.5 | 4 |
| 3.65 | 4 |
| 4 | 4 |
| 3.85 | 4 |
| 3.5 | 3.85 |
| 3.65 | 3.85 |
| 3.85 | 3.85 |
+-------+-------+
可以看到其实上述语句的作用于内连接的作用一样,也可以写成:
select * from (select distinct Score from Scores) as s1 inner join (select distinct Score from Scores) as s2 on s1.Score <= s2.Score;
如果取左边那一行为限制,改为:
select * from (select distinct Score from Scores) as s1 join (select distinct Score from Scores) as s2 on s1.Score <= s2.Score group by s1.Score;
那么我们可以看到其实是在统计各个分数的排名大小情况,也就是说,比如4,只有4 <= 4。所以,在统计的时候,我们可以发现在左边的那一列中,4出现的次数就为1,而最小的那个出现的次数为4,其实也是它的排名,如果用一个升序排列,就可以直接把各个分数的排名都统计出来。然后再将这张临时表格和原来的分数表格做一个连接,取得我需要的结果。
真正的代码如下:
select s2.Score,s1.Rank from (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;