问题描述:
编写一个 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 |
+-------+------+
————————————————
所用的DDL:
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for scores
-- ----------------------------
DROP TABLE IF EXISTS `scores`;
CREATE TABLE `scores` (
`id` int(0) NOT NULL AUTO_INCREMENT,
`score` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of scores
-- ----------------------------
INSERT INTO `scores` VALUES (1, '3.50');
INSERT INTO `scores` VALUES (2, '3.65');
INSERT INTO `scores` VALUES (3, '4.00');
INSERT INTO `scores` VALUES (4, '3.85');
INSERT INTO `scores` VALUES (5, '4.00');
INSERT INTO `scores` VALUES (6, '3.65');
SET FOREIGN_KEY_CHECKS = 1;
sql语句,拿去就可以运行:
SELECT s1.score as 'score', (SELECT count(DISTINCT s2.score)+1 from scores as s2 where s2.score>s1.score) as 'rank' from scores s1 ORDER BY s1.score desc
有一定的难度,我第一做,没啥好办法,看了答案才恍然大悟的。
我要刷100道算法题,第74道