mysql 中根据查询结果排名 博客分类： 数据库mysql

 

参考：https://blog.csdn.net/t2080305/article/details/82223415

 

table：

CREATE TABLE `t_rakeback_stat` (

  `USER_ID` varchar(10) NOT NULL COMMENT '邀请人（自己）用户ID',

  `USER_NAME` varchar(32) NOT NULL COMMENT '用户名',

  `FRIEND_NUM` int(6) NOT NULL COMMENT '好友数量',

  `RAKEBACK_AMOUNT_TOTAL` decimal(20,8) NOT NULL COMMENT '返佣收益总数量',

  `RAKEBACK_AMOUNT_REMAIN` decimal(20,8) NOT NULL COMMENT '返佣收益剩余数量',

  `RAKEBACK_COIN_CODE` tinyint(4) NOT NULL COMMENT '返佣金额币种',

  `CREATE_TIME` datetime NOT NULL COMMENT '创建时间',

  `UPDATE_TIME` datetime NOT NULL COMMENT '更新时间',

  PRIMARY KEY (`USER_ID`),

  KEY `idx_rank` (`RAKEBACK_AMOUNT_TOTAL`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='邀请返佣统计表';

 

排行榜列表

SELECT
		    temp.userId,
		    temp.userName,
                    temp.rakebackAmountTotal,
		    temp.rank
	    FROM
		    (
		        SELECT
		            ta.user_id AS userId,
                            ta.user_name AS userName,
                            ta.rakeback_amount_total AS rakebackAmountTotal,
		            @index := @index + 1,
		            @rank := (CASE
				WHEN @temp_rakeback_amount_total = ta.rakeback_amount_total THEN @rank
				WHEN @temp_rakeback_amount_total := ta.rakeback_amount_total THEN @index
				WHEN @temp_rakeback_amount_total = 0 OR @temp_rakeback_amount_total IS NULL THEN @index
				END) AS rank
		        FROM
		        (
			    SELECT 
		              	user_id,
				user_name,
		              	rakeback_amount_total 
		            FROM
				t_rakeback_stat 
		          ORDER BY 
				rakeback_amount_total DESC
		        ) AS ta,
		        ( SELECT @rank := 0, @rowtotal := NULL, @index := 0 ) r
		    ) AS temp