mysql 实现DENSE_RANK-优快云博客

本文介绍了一个复杂的SQL查询案例，用于从app_qixi_turntable表中按积分降序获取记录，并为相同积分的用户分配相同的排名。通过使用CASE语句和变量来实现这一功能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SELECT
	t.*, CASE
WHEN @integral = integral THEN
	@pm := @pm
ELSE
	@pm := @pm + 1
END pm,
 @integral := integral ints
FROM
	(
		(
			SELECT
				*
			FROM
				app_qixi_turntable
			WHERE
				`day` = '2017-05-15'
			ORDER BY
				integral DESC
		) t,
		(SELECT @pm := 0, @integral := 0) oth
	)

sql文件

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for app_qixi_turntable
-- ----------------------------
DROP TABLE IF EXISTS `app_qixi_turntable`;
CREATE TABLE `app_qixi_turntable` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `user_name` varchar(255) DEFAULT NULL,
  `integral` decimal(10,0) DEFAULT '0' COMMENT '积分',
  `day` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of app_qixi_turntable
-- ----------------------------
INSERT INTO `app_qixi_turntable` VALUES ('1', '24', '13430', '819', '2017-05-15');
INSERT INTO `app_qixi_turntable` VALUES ('2', '1', '13430', '820', '2017-05-15');
INSERT INTO `app_qixi_turntable` VALUES ('3', '2', '1343006', '820', '2017-05-15');
INSERT INTO `app_qixi_turntable` VALUES ('4', '3', '13430', '825', '2017-05-15');
INSERT INTO `app_qixi_turntable` VALUES ('5', '4', '1338', '826', '2017-05-15');
INSERT INTO `app_qixi_turntable` VALUES ('6', '5', '134318', '825', '2017-05-15');
INSERT INTO `app_qixi_turntable` VALUES ('7', '6', '134308', '829', '2017-05-15');
INSERT INTO `app_qixi_turntable` VALUES ('8', '7', '134300', '821', '2017-05-15');
INSERT INTO `app_qixi_turntable` VALUES ('9', '8', '1343008', '821', '2017-05-15');
INSERT INTO `app_qixi_turntable` VALUES ('10', '9', '1343008', '821', '2017-05-15');
INSERT INTO `app_qixi_turntable` VALUES ('11', '23', '1348', '820', '2017-05-15');