分数排名
1、题目:
编写一个 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 |
+-------+------+
2、解题步骤:
(1) 创建表:
CREATE TABLE `scores` (
`Id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Id',
`score` double DEFAULT NULL COMMENT '成绩',
PRIMARY KEY (`Id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '成绩表' ROW_FORMAT = Dynamic;
(2) 插入数据:
INSERT INTO `scores` VALUES (1, 3.5);
INSERT INTO `scores` VALUES (2, 3.65);
INSERT INTO `scores` VALUES (3, 4);
INSERT INTO `scores` VALUES (4, 3.85);
INSERT INTO `scores` VALUES (5, 4);
INSERT INTO `scores` VALUES (6, 3.65);
(3) 查询SQL:
a、方案一:MySQL5.7
SELECT a.score AS score
, (
SELECT COUNT(DISTINCT b.score)
FROM scores b
WHERE b.score>=a.score
) AS Rank
FROM scores a
ORDER BY a.score DESC;
b、方案二:MySQL8.0
SELECT score, dense_rank() OVER (ORDER BY score DESC) AS dese_rank
FROM scores;