MYSQL 排名方式汇总

DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
  `id` varchar(10) DEFAULT NULL,
  `nickname` varchar(5) DEFAULT NULL,
  `score` int(5) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `score` VALUES ('1', '01', '100');
INSERT INTO `score` VALUES ('7', '079', '65');
INSERT INTO `score` VALUES ('2', '023', '99');
INSERT INTO `score` VALUES ('6', '068', '95');
INSERT INTO `score` VALUES ('3', '003', '99');
INSERT INTO `score` VALUES ('4', '045', '96');
INSERT INTO `score` VALUES ('5', '035', '95');
//方式1

select s.*,@a:=@a+1 as rank from (select * from score order by score desc)s,(select @a:=0)d

//方式2
select
tmp.id,tmp.nickname,tmp.score,
-- 顺序一直在变大
@j:=@j+1 as j,
-- 前后二次排序值不同时
@k:=(case when @pre_score=tmp.score then @k else @j end) as rank,
@pre_score:=tmp.score as pre_score
from
(
select * from score order by score desc
) tmp,
(select @k :=0,@j:=0, @pre_score:=0) sdcore

//方式3
select
tmp.id,tmp.nickname,tmp.score,
@j:=@j+1 as j,
@k:=(case when @pre_score=tmp.score then @k else @k:=@k+1 end) as rank,
@pre_score:=tmp.score as pre_score
from
(
select * from score order by score desc
) tmp,
(select @k :=0,@j:=0, @pre_score:=0) sdcore