项目9: 成绩中国式排名(难度:中等)
编写一个 SQL 查询来实现分数排名。如果两个分数相同,则两个分数排名(Rank)相同。请注意,评分后的下一个名次应该是上一个连续的整数值,换句话说,名次之间不应该有“间隔”。
创建以下score表:
+----+-------+
| 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 TABLE score ( id int(11) NOT NULL, score float(5,2) DEFAULT NULL, PRIMARY KEY (`id`)); -- 插入数据 INSERT INTO `score` VALUES ('1', '3.50'); INSERT INTO `score` VALUES ('2', '3.65'); INSERT INTO `score` VALUES ('3', '4.00'); INSERT INTO `score` VALUES ('4', '3.85'); INSERT INTO `score` VALUES ('5', '4.00'); INSERT INTO `score` VALUES ('6', '3.65');
最终SQL语句:
select score, (select count(distinct score) from score as s2 where s2.score >= s1.score) Rank from score as s1 order by score DESC;