问题:
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 | +-------+------+
解决:
① 对于每一个分数,找出表中有多少个大于或等于该分数的分数,不包括其本身,然后按降序排列即可。1515 ms
SELECT Score,
(SELECT COUNT(DISTINCT Score) FROM Scores WHERE Score >= s.Score) Rank
FROM Scores s ORDER BY Score DESC;
② 使用内交,Join是Inner Join的简写形式,自己和自己内交,条件是右表的分数大于等于左表,然后群组起来根据分数的降序排列。1845 ms
SELECT s1.Score,COUNT(DISTINCT s2.Score) Rank
FROM Scores s1 JOIN Scores s2 ON s1.Score <= s2.Score
GROUP BY s1.Id
ORDER BY s1.Score DESC;
③ 用了两个变量,变量使用时其前面需要加@,这里的:= 是赋值的意思,如果前面有Set关键字,则可以直接用=号来赋值,如果没有,则必须要使用:=来赋值,两个变量rank和pre,其中rank表示当前的排名,pre表示之前的分数,下面代码中的<>表示不等于,如果左右两边不相等,则返回true或1,若相等,则返回false或0。初始化rank为0,pre为-1,然后按降序排列分数,对于分数4来说,pre赋为4,和之前的pre值-1不同,所以rank要加1,那么分数4的rank就为1,下面一个分数还是4,那么pre赋值为4和之前的4相同,所以rank要加0,所以这个分数4的rank也是1,以此类推就可以计算出所有分数的rank了。906 ms
SELECT Score,
@rank := @rank + (@pre <> (@pre := Score)) Rank
FROM Scores,(SELECT @rank := 0, @pre := -1) INIT
ORDER BY Score DESC;