mysql的分类排名_mysql高低排名

数据准备

CREATE TABLE `user_score` (

`id` bigint(64) NOT NULL AUTO_INCREMENT,

`userId` bigint(64) DEFAULT NULL,

`score` double DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

insert into `user_score` (`id`, `userId`, `score`) values('1','1','1');

insert into `user_score` (`id`, `userId`, `score`) values('2','2','20');

insert into `user_score` (`id`, `userId`, `score`) values('3','3','20');

insert into `user_score` (`id`, `userId`, `score`) values('4','4','60');

insert into `user_score` (`id`, `userId`, `score`) values('5','5','90');

insert into `user_score` (`id`, `userId`, `score`) values('6','6','100');

insert into `user_score` (`id`, `userId`, `score`) values('7','7',NULL);

A相同分数排名不一样

SELECT

t1.*,

@rank := @rank + 1 rank

FROM

(SELECT

a.`userId`,

a.`score`

FROM

`user_score` a

ORDER BY a.`score` DESC) t1,

(SELECT

@rank := 0) t2

B相同分数排名一样

SELECT

t1.*,

(

CASE

WHEN @score = t1.score THEN @rank

WHEN @score := t1.score THEN @rank := @rank + 1

WHEN @score = 0 OR @score IS NULL THEN @rank := @rank + 1

END

) rank

FROM

(SELECT

userId,

score

FROM

user_score a

ORDER BY a.`score` DESC) t1,

(SELECT

@rank := 0,

@score := NULL) t2 ;

C相同分数占用排名位

SELECT

b.userId,

b.score,

b.rank

FROM

(SELECT

a.*,

@index := @index + 1,

@rank := (

CASE

WHEN @temp_view_count = a.score THEN @rank

WHEN @temp_view_count := a.score THEN @index

WHEN @temp_view_count = 0 OR @temp_view_count IS NULL THEN @index

END

) rank

FROM

(SELECT

userId,

score

FROM

user_score

ORDER BY score DESC) a,

(SELECT

@rank := 0,

@rowtotal := NULL,

@index := 0) r) b