MySQL 排序和变量

ROW_NUMBER() ：连续排名
DENSE_RANK() ：并列连续排名
RANK() ：并列跳跃排名
复制

CREATE TABLE `scores` (
  `id` int(11) NOT NULL COMMENT 'ID',
  `name` varchar(255) DEFAULT NULL COMMENT '姓名',
  `course_id` varchar(255) DEFAULT NULL COMMENT '课程ID',
  `score` int(11) DEFAULT NULL COMMENT '成绩',
  PRIMARY KEY (`id`)
) ;
复制

1--ROW_NUMBER()实现 窗口函数，mysql8.0以下版本不支持，了解一下就行
2SELECT score,
3ROW_NUMBER() OVER (ORDER BY score DESC) ranking
4FROM SCORES;

复制

1--使用变量实现
2-- ：=是mysql中的赋值
3--这里的cur_rank是先+1然后再赋值给自己
4SELECT score, 
5       (@cur_rank := @cur_rank + 1) ranking  
6FROM SCORES s, 
7(SELECT @cur_rank := 0) r
8ORDER BY score DESC;

复制

1--ROW_NUMBER()结合PARTITION BY
2SELECT t.course_id,
3       t.score, 
4       ROW_NUMBER() OVER(PARTITION BY t.course_id order by t.score DESC) as ranking
5FROM  SCORES t;
复制

 1-- 变量和IF实现
 2-- 新增的@pre_rank用来记录courseid，通过IF语句判断每一行课程id和上一行是否相等
 3SELECT t.course_id,
 4       t.score, 
 5       @cur_rank :=IF(@pre_rank=course_id,@cur_rank+1,1) as ranking,
 6       @pre_rank :=course_id
 7FROM SCORES t, 
 8(SELECT @cur_rank := 0,@pre_rank=null) r
 9order by course_id ASC,score DESC;
10
11-- 变量和CASE WHEN实现
12SELECT t.course_id,
13     t.score, 
14     case when @pre_rank=course_id then @cur_rank:=@cur_rank+1 
15          when @pre_rank :=course_id then @cur_rank:=1
16       end as ranging
17FROM SCORES t, 
18(SELECT @cur_rank := 0,@pre_rank=null) r
19order by course_id ASC,score DESC;

复制

1--DENSE_RANK()实现  窗口函数，mysql 8.0以下版本不支持
2SELECT  num,
3        DENSE_RANK() OVER(ORDER BY rank DESC) 
4FROM SCORES;
复制

 1--变量和IF实现
 2--这里的 @pre_rank变量用来记录成绩，然后通过IF语句判断和上一行的成绩是否相等
 3SELECT score,
 4IF(@pre_rank = score, @cur_rank, @cur_rank := @cur_rank + 1) ranking,
 5@pre_rank := score
 6FROM SCORES s, (SELECT @cur_rank :=0, @pre_rank = NULL) r
 7ORDER BY score DESC;
 8
 9--变量和case when 
10SELECT score,
11       case when @pre_rank = score then @cur_rank -- 即@cur_rank:= @cur_rank
12            when @pre_rank := score then @cur_rank := @cur_rank + 1
13       as ranking
14FROM SCORES s, (SELECT @cur_rank :=0, @pre_rank = NULL) r
15ORDER BY score DESC;

复制

1--DENSE_RANK() 结合PARTITION BY
2SELECT course_id, 
3       score,
4       DENSE_RANK() OVER(PARTITION BY course_id ORDER BY score DESC)
5FROM scores;

复制

 1-- 变量和IF语句
 2-- @pre_rank 记录成绩， @pre_course_id 记录科目
 3-- 可以看出， 用来记录源数据的变量初始值一般是null
 4SELECT course_id,
 5       score,
 6       IF(@pre_rank = score, @cur_rank, @cur_rank := @cur_rank + 1),
 7       @pre_rank := score,
 8       IF(@pre_course_id = course_id, @cur_rank, @cur_rank := 1) ranking,
 9       @pre_course_id := course_id
10FROM SCORES s, 
11(SELECT @cur_rank := 0, @pre_rank = NULL,@pre_course_id := NULL) r
12ORDER BY course_id ASC,score DESC;

复制

1--RANK() 函数 窗口函数，mysql 8.0以下版本不支持
2SELECT *,
3       RANK() OVER(ORDER BY score DESC)
4FROM SCORES;

复制

 1-- 变量和 if 实现
 2-- 新增的@rank_counter用来记录原始排名
 3SELECT score,
 4       @rank_counter := @rank_counter + 1,
 5       IF(@pre_rank = score, @cur_rank, @cur_rank := @rank_counter) ranking,
 6       @pre_rank := score
 7FROM SCORES s, 
 8(SELECT @cur_rank :=0, @pre_rank := NULL, @rank_counter := 0) r 
 9ORDER BY score DESC;
10
11-- 变量和CASE WHEN 实现
12SELECT score,
13       @rank_counter := @rank_counter + 1,
14       case when @pre_rank = score then @cur_rank --即@cur_rank :=@cur_rank
15            when @pre_rank := score then @cur_rank := @rank_counter
16       end as ranking
17FROM SCORES s, (SELECT @cur_rank :=0, @pre_rank := NULL, @rank_counter := 0) r
18ORDER BY score DESC;

复制

1--RANK()结合PARTITION BY
2SELECT course_id, 
3       score,
4       RANK() OVER(PARTITION BY course_id ORDER BY score DESC)
5FROM SCORES;
复制

 1-- 变量和IF语句
 2SELECT course_id,
 3       score,
 4       @rank_counter := IF(@pre_course_id = s.course_id,@rank_counter + 1,1),
 5       IF(@pre_course_id = s.course_id,IF(@pre_rank = s.score, @cur_rank, @cur_rank := @rank_counter),@cur_rank := 1) ranking,
 6       @pre_rank := s.score,
 7       @pre_course_id := s.course_id
 8FROM SCORES s, 
 9(SELECT @cur_rank :=0, @pre_rank := NULL, @rank_counter := 0,@cur_couse:=null) r
10ORDER BY course_id ASC,score DESC;

复制