MySQL 三种排名查询

Oracle 自带的函数 RANK(或 DENSE_RANK)，由于 MySQL 没有类似函数稍微繁琐一点。

 

数据准备：

DROP TABLE IF EXISTS `tb_test`;
CREATE TABLE `tb_test`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `subject` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 13 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of tb_test
-- ----------------------------
INSERT INTO `tb_test` VALUES (1, 'bob', 'Math', 55);
INSERT INTO `tb_test` VALUES (2, 'bob', 'Chinese', 80);
INSERT INTO `tb_test` VALUES (3, 'bob', 'English', 75);
INSERT INTO `tb_test` VALUES (4, 'any', 'Math', 98);
INSERT INTO `tb_test` VALUES (5, 'any', 'Chinese', 66);
INSERT INTO `tb_test` VALUES (6, 'any', 'English', 81);
INSERT INTO `tb_test` VALUES (7, 'candy', 'Math', 77);
INSERT INTO `tb_test` VALUES (8, 'candy', 'Chinese', 82);
INSERT INTO `tb_test` VALUES (9, 'candy', 'English', 79);
INSERT INTO `tb_test` VALUES (10, 'bill', 'Math', 65);
INSERT INTO `tb_test` VALUES (11, 'bill', 'Chinese', 46);
INSERT INTO `tb_test` VALUES (12, 'bill', 'English', 99);

SET FOREIGN_KEY_CHECKS = 1;

 

排序一：（值相同，排列不同）

SQL：定义变量@curRank保存排名

SELECT 
	NAME,
	SUM( score ) AS totalScore,
	@curRank := @curRank + 1 AS rank 
FROM
	tb_test tt,
	( SELECT @curRank := 0 ) temp 
GROUP BY
NAME 
ORDER BY
	rank

结果：

 

排序二：（值相同，排名也相同；排名值连续递增）

SQL：定义变量@preRank保存上次totalScore值，@curRank保存排名

SELECT
	result.NAME,
	result.totalScore,
CASE
	WHEN @prevRank = totalScore THEN
	@curRank 
	WHEN @prevRank := totalScore THEN
	@curRank := @curRank + 1 
	END AS rank 
FROM
	( SELECT NAME, SUM( score ) AS totalScore FROM tb_test GROUP BY NAME ) result,
	( SELECT @curRank := 0, @prevRank := NULL ) temp 
ORDER BY
rank

结果：

注：之所以没有选择按照上面的方式，是因为SUM求和后未排序便开始与前一个对比，造成了相同结果排名不同，所以第二种时先进行了求和然后用结果来直接进行对比

 

排序三：（值相同，排名相同，排名值自然递增）

SQL：定义变量@preRank保存上次totalScore值，@curRank保存上次排名，@incRank自然排名

SELECT 
	NAME,
	totalScore,
	rank 
FROM
	(
		SELECT
			tt.NAME,
			tt.totalScore,
			@curRank :=
		IF
			( @preRank = tt.totalScore, @curRank, @incRank ) AS rank,
			@preRank := tt.totalScore,
			@incRank := @incRank + 1 
		FROM
			( SELECT NAME, SUM( score ) AS totalScore FROM tb_test GROUP BY NAME ORDER BY totalScore ) tt,
			( SELECT @curRank := 0, @prevRank := NULL, @incRank := 1 ) t 
		ORDER BY
			rank 
	) temp

结果：