mysql实现排名

1.mysql常用的排名函数

mysql在8.0提供了三个函数进行排名，RANK、DENSE_RANK和ROW_NUMBER函数，如果低于mysql8.0需要自己实现                                              

2. RANK、DENSE_RANK和ROW_NUMBER使用

1.rank、dense_rank、row_number函数的使用

（1）创建表语句

CREATE TABLE `score_ranking` (
  `rank_id` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `num_id` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `score` decimal(18,1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

（2）插入数据

INSERT INTO `score_ranking`(`rank_id`, `num_id`, `score`) VALUES ('01', '01', 80.0);
INSERT INTO `score_ranking`(`rank_id`, `num_id`, `score`) VALUES ('01', '02', 90.0);
INSERT INTO `score_ranking`(`rank_id`, `num_id`, `score`) VALUES ('01', '03', 99.0);
INSERT INTO `score_ranking`(`rank_id`, `num_id`, `score`) VALUES ('02', '01', 70.0);
INSERT INTO `score_ranking`(`rank_id`, `num_id`, `score`) VALUES ('02', '02', 60.0);
INSERT INTO `score_ranking`(`rank_id`, `num_id`, `score`) VALUES ('02', '03', 80.0);
INSERT INTO `score_ranking`(`rank_id`, `num_id`, `score`) VALUES ('03', '01', 80.0);
INSERT INTO `score_ranking`(`rank_id`, `num_id`, `score`) VALUES ('03', '02', 80.0);
INSERT INTO `score_ranking`(`rank_id`, `num_id`, `score`) VALUES ('03', '03', 80.0);
INSERT INTO `score_ranking`(`rank_id`, `num_id`, `score`) VALUES ('04', '01', 50.0);
INSERT INTO `score_ranking`(`rank_id`, `num_id`, `score`) VALUES ('04', '02', 30.0);
INSERT INTO `score_ranking`(`rank_id`, `num_id`, `score`) VALUES ('04', '03', 20.0);
INSERT INTO `score_ranking`(`rank_id`, `num_id`, `score`) VALUES ('05', '01', 76.0);
INSERT INTO `score_ranking`(`rank_id`, `num_id`, `score`) VALUES ('05', '02', 87.0);
INSERT INTO `score_ranking`(`rank_id`, `num_id`, `score`) VALUES ('06', '01', 31.0);
INSERT INTO `score_ranking`(`rank_id`, `num_id`, `score`) VALUES ('06', '03', 34.0);
INSERT INTO `score_ranking`(`rank_id`, `num_id`, `score`) VALUES ('07', '02', 89.0);
INSERT INTO `score_ranking`(`rank_id`, `num_id`, `score`) VALUES ('07', '03', 98.0);
INSERT INTO `score_ranking`(`rank_id`, `num_id`, `score`) VALUES ('08', '02', 89.0);
INSERT INTO `score_ranking`(`rank_id`, `num_id`, `score`) VALUES ('09', '02', 89.0);

（3）使用rank函数排名（跳跃排名）

SELECT s.*,
rank() OVER (ORDER BY score DESC) ranking
FROM score_ranking s;

结果如下：

 （4）使用dense_rank函数（不跳跃连续排名）

SELECT s.*,
dense_rank() OVER (ORDER BY score DESC) ranking
FROM score_ranking s;

结果如下：

 （5）使用row_number函数排名（不分组）

SELECT s.*,
ROW_NUMBER() OVER (ORDER BY score DESC) ranking
FROM score_ranking s;

结果如下：

 3.mysql8.0之前实现排序

   1.使用if和变量实现跳跃排名

select s.id,s.total_score,
@rank_counter := @rank_counter + 1 as rank_counter,
if(@pre_score = s.total_score,@cur_rank,@cur_rank := @rank_counter) ranking,
@pre_score := s.total_score 
from coll_sales_department_summary s, (SELECT @cur_rank :=0, @pre_score := NULL, @rank_counter := 0) r
ORDER BY cast(s.total_score as DECIMAL(12,0)) DESC;

结果如下：

 2.使用if和变量实现非跳跃排名

select s.id,s.total_score,
if(@pre_score = s.total_score,@cur_rank,@cur_rank :=@cur_rank+1) ranking,
@pre_score := s.total_score 
from coll_sales_department_summary s, (SELECT @cur_rank :=0, @pre_score := NULL) r
ORDER BY cast(s.total_score as DECIMAL(12,0)) DESC;

结果如下：

注：@cur_rank :=@cur_rank+1中":="代表赋值操作