mysql rank() over,dense_rank() over,row_number() over的区别

mysql rank() over,dense_rank() over,row_number() over的区别

问题的由来

准备

-- ----------------------------
-- Table structure for scores
-- ----------------------------
DROP TABLE IF EXISTS `scores`;
CREATE TABLE `scores`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `course` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '课程',
  `student_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '名字',
  `score` bigint NOT NULL COMMENT '分数',
  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, '数学', '张三', 100);
INSERT INTO `scores` VALUES (2, '数学', '李四', 95);
INSERT INTO `scores` VALUES (3, '数学', '王五', 99);
INSERT INTO `scores` VALUES (4, '数学', '赵六', 91);
INSERT INTO `scores` VALUES (5, '数学', '六六', 100);

SET FOREIGN_KEY_CHECKS = 1;

第一个：rank() over

并列排名、会跳跃

SELECT
	course,
	student_name,
	score ,
	rank() over(ORDER BY score desc ) as '排名'
FROM
	scores;

第二个、dense_rank() over

排名并列，不跳跃

SELECT
	course,
	student_name,
	score ,
	DENSE_RANK() over(ORDER BY score desc ) as '排名'
FROM
	scores;

第三个、row_number() over

不重复排名

SELECT
	course,
	student_name,
	score ,
	row_number() over(ORDER BY score desc ) as '排名'
FROM
	scores;