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

描述

根据 Score 进行排序，并标明 Rank 排名

代码

-- 方法一
-- 通过局部变量来进行 Rank 的累加

select 
	Score,
	@rank := @rank + (@prev <> @prev := Score) Rank
from 
	Scores,
	(select @rank := 0 , @prev := -1) init
order by Score desc;

-- 方法二

select 
	Score,
	(select count(distinct Score) from Scores where Score >= s.Score) Rank
from 
	Scores s
order by Score desc;

-- 方法三
-- 由于预处理生成的 tmp，效率比方法二要高

select 
	Score,
	(select count(*) from (select distinct Score s from Scores) tmp where s >= Score) Rank
from 
	Scores
order by Score desc;

-- 方法四

select 
	t.Score,
	count(distinct s.Score) Rank 
from
	Scores s inner join Scores t on s.Score >= t.Score 
group by t.Id
order by t.Score desc;