mysql rank() over,dense_rank() over,row_number() over The difference between

The origin of the problem

Get ready

-- ----------------------------
-- 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 ' Course ',
  `student_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT ' name ',
  `score` bigint NOT NULL COMMENT ' fraction ',
  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, ' mathematics ', ' Zhang San ', 100);
INSERT INTO `scores` VALUES (2, ' mathematics ', ' Li Si ', 95);
INSERT INTO `scores` VALUES (3, ' mathematics ', ' Wang Wu ', 99);
INSERT INTO `scores` VALUES (4, ' mathematics ', ' Zhao Liu ', 91);
INSERT INTO `scores` VALUES (5, ' mathematics ', ' 66 ', 100);

SET FOREIGN_KEY_CHECKS = 1;

first ：rank() over

Parallel ranking 、 I can jump

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

the second 、dense_rank() over

Rank side by side , Don't jump

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

Third 、row_number() over

Don't repeat ranking

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