MySQL统计排序，同分数同排名

        在一些应用场景中，我们经常会遇到一些排名的问题，比如按成绩、年级、热度排名。排名也有多种排名方式，如直接排名、分组排名，排名有间隔(同维度同排名，排名不连续)或排名无间隔等等。

创建测试表

CREATE TABLE t_scores (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`stu_number` INT ( 11 ) NOT NULL COMMENT '学号',
`score` INT ( 11 ) NOT NULL  COMMENT '分数',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = INNODB DEFAULT CHARACTER SET = utf8 COLLATE = utf8_general_ci;

insert into t_scores (stu_number,score) values (1001,89),(1002,99),(1003,96),(1004,96),(1005,92),(1006,90),(1007,90),(1008,94);

 

 

普通排名

按分数高低直接排名，从1开始，往下排，类似于row number。下面我们给出查询语句及排名结果。

SELECT stu_number, score, @curRank := @curRank + 1 AS rank
FROM t_scores, (
SELECT @curRank := 0
) r
ORDER BY score desc;

 

 排名无间隔

# 分数相同，名次相同，排名无间隔
# 查询语句
SELECT stu_number, score, 
CASE 
WHEN @prevRank = score THEN @curRank 
WHEN @prevRank := score THEN @curRank := @curRank + 1
END AS rank
FROM t_scores, 
(SELECT @curRank :=0, @prevRank := NULL) r
ORDER BY score desc;

 间隔排序

# 另外一种排名方式是相同的值排名相同，相同值的下一个名次应该是跳跃整数值，即排名有间隔。
# 查询语句
SELECT stu_number, score, rank FROM
(SELECT stu_number, score,
@curRank := IF(@prevRank = score, @curRank, @incRank) AS rank, 
@incRank := @incRank + 1, 
@prevRank := score
FROM t_scores, (
SELECT @curRank :=0, @prevRank := NULL, @incRank := 1
) r 
ORDER BY score desc) s;