问题
编写一个 SQL 查询来实现分数排名。如果两个分数相同，则两个分数排名（Rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。
| Id | Score |
|---|---|
| 1 | 3.50 |
| 2 | 3.65 |
| 3 | 4.00 |
| 4 | 3.85 |
| 5 | 4.00 |
| 6 | 3.65 |
例如，根据上述给定的 Scores 表，你的查询应该返回（按分数从高到低排列）：
| Score | Rank |
|---|---|
| 4.00 | 1 |
| 4.00 | 1 |
| 3.85 | 2 |
| 3.65 | 3 |
| 3.65 | 3 |
| 3.50 | 4 |
准备
create database leecote178;
use leecote178;
create table scores (
id varchar(20),
score float(4,2)
);
insert into scores values
(1,3.5),
(2,3.65),
(3,4.00),
(4,3.85),
(5,4.00),
(6,3.65);
知识点
解题前先理解变量赋值用法
https://www.jianshu.com/p/d732d1cb1a89?utm_campaign=haruki&utm_content=note&utm_medium=reader_share&utm_source=weixin
解答
#方法一:
select score,rank from (select score,@n:=if(@s=score,@n,@n+1) as rank,@s:=score from scores s,(select @n:=0,@s=null) as b order by score desc) a;
#方法二:
select score,@a := @a + (@pre <> (@pre := Score)) as rank
from scores,(select @a := 0, @pre := -1) t
order by score desc;
解释
整理了一下几个不好理解的点：
@a 类似在 Oracle 中的rownum，可以在生成结果内附加上一列序列号，可以近似理解为查询后加上行号；
@a := @a + 1 实际上是赋值，旧值+1变为新值赋给@a；
实际上并没有直接 @a+1 那么简单，还要先去判断分数是否与前一行相同，所以引入 @pre 来记录：
先将Score赋值给： @pre := Score；
然后判断之前的 @pre 是否与赋值后的 @pre 不相同 ==> （@pre <> (@pre := Score)） “<>” 就是 "!=" 的意思；
两者不同，判断结果为真，则取1； 两者相同，判断结果为假，取0， 最后再用 0或1 加上 @a 即为当前行分数的排名；
(select @a := 0, @pre := -1) t 为初始化 @a 和 @pre 的开始值；
@pre 初始值为 -1 为的是防止Score有 0 分的出现；
@第一次比较Score值的时候，@pre初始值肯定和Score不同， 所以第一次比较 @a 必然会 +1，所以@a要从0开始；
最后以将结果根据Score进行倒序展示；
顺便一提的是，这个代码的结果生成的rank带有小数点，可以考虑使用 cast() 方法来消除排名的小数点；
网友评论