mysql查询中怎样排名_mysql 中根据查询结果排名

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