心血来潮刷了一波LeetCode,遇见个有意思的sql题目。
题目连接:https://leetcode-cn.com/problems/rank-scores/
编写一个 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 |
±------±-----+
一、先搞个顺序排名,分数相等顺序排名
SELECT
a.Score,
@rowNum1 := @rowNum1 + 1 AS Rank
FROM
Scores a,
( SELECT @rowNum1 := 0 ) b
ORDER BY
a.Score DESC;
(SELECT @rowNum1 :=0) b 的作用是:
在同一个select语句中给变量rowNum1 赋初始值。
等同于,两个sql语句,第一个先赋值,第二个再select.
二、实现同分数同名次
引入一个变量记录当前行Score的值,如果下一行Score值等于变量值,rowNum1 不增加,否则rowNum1 +1,且重新为变量赋值当前Score值。
SELECT
a.Score,
CASE
WHEN a.Score = @rowNum2 THEN @rowNum1
WHEN @rowNum2 := a.Score THEN @rowNum1 := @rowNum1 + 1
END AS Rank
FROM
Scores a,
( SELECT @rowNum1 := 0, @rowNum2 := NULL ) b
ORDER BY
a.Score DESC;
三、这里有个坑,Score为0的行返回NULL
Score为0时不走任何分支直接返回了null,case when后增加else @rowNum1:=@rowNum1+1
SELECT
a.Score,
CASE
WHEN a.Score = @rowNum2 THEN @rowNum1
WHEN @rowNum2 := a.Score THEN @rowNum1 := @rowNum1 + 1
ELSE @rowNum1 := @rowNum1 + 1
END AS Rank
FROM
Scores a,
( SELECT @rowNum1 := 0, @rowNum2 := NULL ) b
ORDER BY
a.Score DESC;
或者使用if函数
SELECT
t.Score,
t.Rank
FROM
(
SELECT
a.Score,
IF
( a.Score = @rowNum2, @rowNum1 + 0, @rowNum1 := @rowNum1 + 1 ) AS Rank,
@rowNum2 := a.Score
FROM
Scores a,
( SELECT @rowNum1 := 0, @rowNum2 := NULL ) b
ORDER BY
a.Score DESC
) t;
if(a>1,A,B): 如果为真返回A,否则返回B。这里返回的@rowNum1是字符串,题目要求排名使用数字,所以在@rowNum1 后+ 0,rowNum1 会转成数字。