【sql】leetcode.178分数排名

编写一个 SQL 查询来实现分数排名。

如果两个分数相同，则两个分数排名（Rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。

+----+-------+
| Id | Score |
+----+-------+
| 1  | 3.50  |
| 2  | 3.65  |
| 3  | 4.00  |
| 4  | 3.85  |
| 5  | 4.00  |
| 6  | 3.65  |
+----+-------+

一、函数

• row_number(): 同薪不同名，相当于行号，例如3000、2000、2000、1000排名后为1、2、3、4
• rank(): 同薪同名，有跳级，例如3000、2000、2000、1000排名后为1、2、2、4
• dense_rank(): 同薪同名，无跳级，例如3000、2000、2000、1000排名后为1、2、2、3
• ntile(): 分桶排名，即首先按桶的个数分出第一二三桶，然后各桶内从1排名，实际不是很常用

以上函数需与over()搭配使用。

select Score, dense_rank() over(order by Score desc) as 'Rank'
#因为Rank为关键字，必须使用‘’
from Scores

二、采用内部变量

select Score, cast(case
when @p = Score then @r+0
when @p := Score then @r := @r +1 
else @r := @r +1
end as signed) as 'Rank'
from Scores,(select @r := 0, @p:=NULL) init
order by Score desc

因为赋值默认为字符串，再后面+0转换类型，转换成了浮点数，使用cast转换为整数。
对于所有不满足@p = Score的记录，将其r+1，且将新的Score赋值给@p。在**Score不为0的情况下，@pre := Score为真；但是在Score等于0的情况下，CASE语句不走任何分支，返回NULL，**此时@p:= Score语句已经执行，后半段@r := @r + 1没有执行，所以，再加一行ELSE @r := @r + 1。

三、单个比较
每个分数都在表b中找比它高的分数，得出当前分数的排名。

select a.Score, (select count(distinct b.Score) from Scores as b where b.Score >= a.Score) as 'Rank'
from Scores as a
order by Score desc