编写一个 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 |
+-------+------+
贡献者
题目来源:力扣(LeetCode)
链接:https://leetcode-cn.com/problems/rank-scores
解题:
官方解题一:
使用两个变量,一个用于当前级别,另一个用于以前的分数。Score分数,
@rank排名,第一个变量
@prev上一个成绩,第二个变量
Rank 排名的别名
初始化了排名和上一个成绩,排名0,上一成绩初始化为-1
(@prev <> (@prev := Score)) 真返回1,假返回0,所以这次成绩和上次成绩相等就是假,返回0,两次排名没有变化,因为加的是0;
如果不相等则是真,返回一,上次排名加1,就是新的排名了。
First one uses two variables, one for the current rank and one for the previous score.
SELECT
Score,
@rank := @rank + (@prev <> (@prev := Score)) Rank
FROM
Scores,
(SELECT @rank := 0, @prev := -1) init
ORDER BY Score desc
相关知识:
用户变量:@变量名。用户变量跟mysql客户端是绑定的,设置的变量,只对当前用户使用的客户端生效。
mysql给变量赋值:使用:=来给变量赋值。
sql语句中,if(A,B,C)表示,如果A条件成立,那么执行B,否则执行C.(与本题无关)
<> 表示不等于;
官方解题二:
按照成绩排序后,每个成绩对应的没有重复的第几个数,就是排名。成绩高于或等于当前学生成绩的学生数量,成绩相同的只记一次,就是该学生的名次。
嵌套查询用来查询排名,
WHERE Score>=s.Score 子句,限制只返回成绩大于或等于当前学生成绩的记录,相当于实现了成绩从高到低排名。
逻辑类似于循环。
This one counts, for each score, the number of distinct greater or equal scores.
SELECT
Score,
(SELECT count(distinct Score) FROM Scores WHERE Score >= s.Score) Rank
FROM Scores s
ORDER BY Score desc
相关知识:
COUNT()
函数返回表中的行数。
嵌套查询:在一条sql中嵌套另一条sql.
官方解题三:
和二思路一样,但是有个子查询,先查出来Score表,效果更好。
Same as the previous one, but faster
because I have a subquery that "uniquifies" the scores first.
Not entirely sure why it's faster,
I'm guessing MySQL makes tmp a temporary table and uses it for every outer Score.
SELECT
Score,
(SELECT count(*) FROM (SELECT distinct Score s FROM Scores) tmp WHERE s >= Score) Rank
FROM Scores
ORDER BY Score desc
官方题解四:
这个方法用上了id,
s.Score左表成绩小于右表成绩,也就是右表成绩大于左表成绩的成绩去重,就是左表排名。
group by语法可以根据给定数据列的每个成员对查询结果进行分组统计,最终得到一个分组汇总表。
将每一个分组的第一个记录组合在一起形成了最终的结果。
本题grder by 后边跟的是id,所以是每列单独分为一个组,
Inspired by the attempt in wangkan2001's answer. Finally Id is good for something :-)
SELECT s.Score, count(distinct t.score) Rank
FROM Scores s JOIN Scores t ON s.Score <= t.score
GROUP BY s.Id
ORDER BY s.Score desc