mysql 根据总分对用户进行排名

接着上篇博客讲。博主已经根据时间查出来了本月和本年的总积分了。

剩下的问题就是根据总积分对用户进行排名了。

这里附上建表语句

CREATE TABLE `launch_info`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `userId` int(10) NOT NULL COMMENT '用户id',
  `time` datetime(0) NOT NULL COMMENT '投放时间',
  `weight` double(10, 2) NOT NULL COMMENT '本次投放重量',
  `score` int(8) NOT NULL COMMENT '本次投放获取的积分',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 9 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;


INSERT INTO `launch_info` VALUES (3, 1, '2019-05-15 20:01:01', 10.00, 100);
INSERT INTO `launch_info` VALUES (4, 1, '2019-05-29 20:05:22', 10.00, 100);
INSERT INTO `launch_info` VALUES (5, 2, '2019-05-12 10:18:20', 20.00, 200);
INSERT INTO `launch_info` VALUES (6, 3, '2019-05-13 10:18:38', 15.00, 10);
INSERT INTO `launch_info` VALUES (7, 2, '2018-05-12 10:12:12', 10.00, 123);
INSERT INTO `launch_info` VALUES (8, 3, '2019-05-30 22:29:29', 10.00, 50);
INSERT INTO `launch_info` VALUES (9, 4, '2019-05-02 21:16:50', 0.00, 0);
INSERT INTO `launch_info` VALUES (10, 3, '2019-04-05 22:38:36', 1.00, 1000);
INSERT INTO `launch_info` VALUES (11, 5, '2019-05-23 23:40:20', 1.00, -1);

然后博主再附上博主已经写好了的查本月总积分的SQL语句

select userId,sum(weight) total_weight 
from launch_info where userId = 1
and DATE_FORMAT(time,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m')

那么， 接下来，我们根据总积分来对用户进行排名

第一种：直接排名，忽略积分相同并列的情况

SELECT
    obj.userId,obj.score,@rownum := @rownum + 1 AS rownum
FROM
    (
        SELECT
            userId,
            score
        FROM
            (SELECT userId,sum(score) score FROM launch_info 
							WHERE DATE_FORMAT(time,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m') GROUP BY userId ORDER BY score desc) u
    ) AS obj,
    (SELECT @rownum := 0) r

第二种：并列排名，但不占位

SELECT
    obj_new.userId,
    obj_new.score,
    obj_new.rownum
FROM
    (
        SELECT
            obj.userId,
            obj.score,
            @rownum := @rownum + 1 AS num_tmp,
            @incrnum := CASE
        WHEN @rowtotal = obj.score THEN
            @incrnum
        WHEN @rowtotal := obj.score THEN
            @rownum
				WHEN @rowtotal = 0 THEN
						@rownum		
        END AS rownum
        FROM
            (SELECT userId,sum(score) score FROM launch_info 
							WHERE DATE_FORMAT(time,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m') GROUP BY userId ORDER BY score desc) AS obj,
            (
                SELECT
                    @rownum := 0 ,@rowtotal := NULL ,@incrnum := 0
            ) r
    ) AS obj_new

第三种：并列而且占位

SELECT
    obj_new.userId,
    obj_new.score,
    obj_new.rownum
FROM
    (
        SELECT
            obj.userId,
            obj.score,
            @rownum := @rownum + 1 AS num_tmp,
            @incrnum := CASE
        WHEN @rowtotal = obj.score THEN
            @incrnum
        WHEN @rowtotal := obj.score THEN
            @rownum
				WHEN @rowtotal = 0 THEN
 						@rownum	
        END AS rownum
        FROM
            (SELECT userId,sum(score) score FROM launch_info 
							WHERE DATE_FORMAT(time,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m') GROUP BY userId ORDER BY score desc) AS obj,
            (
                SELECT
                    @rownum := 0 ,@rowtotal := NULL ,@incrnum := 0
            ) r
    ) AS obj_new

这个问题困扰博主几天了，终于解决了，在此记录一下，并分享给大家