1. 排名分类

1.1 区别RANK，DENSE_RANK和ROW_NUMBER

  • RANK并列跳跃排名，并列即相同的值，相同的值保留重复名次，遇到下一个不同值时，跳跃到总共的排名。
  • DENSE_RANK并列连续排序，并列即相同的值，相同的值保留重复名次，遇到下一个不同值时，依然按照连续数字排名。
  • ROW_NUMBER连续排名，即使相同的值，依旧按照连续数字进行排名。

区别如图：

1.2 分组排名

将数据分组后排名，区别如图：

2. 准备数据

创建一张分数表，里面有字段：分数score，课程号course_id和学生号student_id。
执行如下SQL语句，进行导入数据。

create table score(

  student_id varchar(10),

  course_id varchar(10),

  score decimal(18,1)

);

insert into score values('01' , '01' , 80);

insert into score values('01' , '02' , 90);

insert into score values('01' , '03' , 99);

insert into score values('02' , '01' , 70);

insert into score values('02' , '02' , 60);

insert into score values('02' , '03' , 80);

insert into score values('03' , '01' , 80);

insert into score values('03' , '02' , 80);

insert into score values('03' , '03' , 80);

insert into score values('04' , '01' , 50);

insert into score values('04' , '02' , 30);

insert into score values('04' , '03' , 20);

insert into score values('05' , '01' , 76);

insert into score values('05' , '02' , 87);

insert into score values('06' , '01' , 31);

insert into score values('06' , '03' , 34);

insert into score values('07' , '02' , 89);

insert into score values('07' , '03' , 98);

insert into score values('08' , '02' , 89);

insert into score values('09' , '02' , 89);

查看数据：

3. 不分组排名

3.1 连续排名

  1. 使用ROW_NUMBER实现：

    SELECT score,
    
ROW_NUMBER() OVER (ORDER BY score DESC) ranking
    
FROM score;
  2. 使用变量实现： 
    SELECT s.score, (@cur_rank := @cur_rank + 1) ranking
    
FROM score s, (SELECT @cur_rank := 0) r
    
ORDER BY score DESC;

　　结果如图：

　　　　

3.2 并列跳跃排名

  1. 使用RANK实现：

    SELECT course_id, score,
    
RANK() OVER(ORDER BY score DESC)
    
FROM score;
  2. 使用变量和IF语句实现： 
    SELECT s.score,
    
@rank_counter := @rank_counter + 1,
    
IF(@pre_score = s.score, @cur_rank, @cur_rank := @rank_counter) ranking,
    
@pre_score := s.score
    
FROM score s, (SELECT @cur_rank :=0, @pre_score := NULL, @rank_counter := 0) r
    
ORDER BY s.score DESC;
  3. 使用变量和CASE语句实现： 
    SELECT s.score,
    
@rank_counter := @rank_counter + 1,
    
(
    
  CASE
    
  WHEN @pre_score = s.score THEN @cur_rank
    
  WHEN @pre_score := s.score THEN @cur_rank := @rank_counter
    
  END
    
) ranking
    
FROM score s, (SELECT @cur_rank :=0, @pre_score := NULL, @rank_counter := 0) r
    
ORDER BY s.score DESC;

　　　结果如图：

　　　　

3.3 并列连续排名

  1. 使用DENSE_RANK实现：

    SELECT course_id, score,
    
DENSE_RANK() OVER(ORDER BY score DESC) FROM score;
  1. 使用变量和IF语句实现：

    SELECT s.score,
    
IF(@pre_score = s.score, @cur_rank, @cur_rank := @cur_rank + 1) ranking,
    
@pre_score := s.score
    
FROM score s, (SELECT @cur_rank :=0, @pre_score = NULL) r
    
ORDER BY s.score DESC;
  1. 使用变量和CASE语句实现：

    SELECT s.score,
    
(
    
  CASE
    
  WHEN @pre_score = s.score THEN @cur_rank
    
  WHEN @pre_score := s.score THEN @cur_rank := @cur_rank + 1
    
  END
    
) ranking
    
FROM score s, (SELECT @cur_rank :=0, @pre_score = NULL) r
    
ORDER BY s.score DESC;

    结果如图：

　　　　

4. 分组排名

4.1 分组连续排名

  1. 使用ROW_NUMBER实现：

    SELECT course_id, score,
    
ROW_NUMBER() OVER (PARTITION BY course_id ORDER BY score DESC) ranking FROM score;
  1. 使用变量和IF语句实现：

    SELECT s.course_id, s.score,
    
IF(@pre_course_id = s.course_id, @cur_rank := @cur_rank + 1, @cur_rank := 1) ranking,
    
@pre_course_id := s.course_id
    
FROM score s, (SELECT @cur_rank := 0, @pre_course_id := NULL) r
    
ORDER BY course_id, score DESC;

    结果如图：

　　　　

4.2 分组并列跳跃排名

  1. 使用RANK实现：

    SELECT course_id, score,
    
RANK() OVER(PARTITION BY course_id ORDER BY score DESC)
    
FROM score;
  1. 使用变量和IF语句实现：

    SELECT s.course_id, s.score,
    
IF(@pre_course_id = s.course_id,
    
   @rank_counter := @rank_counter + 1,
    
   @rank_counter := 1) temp1,
    
IF(@pre_course_id = s.course_id,
    
   IF(@pre_score = s.score, @cur_rank, @cur_rank := @rank_counter),
    
   @cur_rank := 1) ranking,
    
@pre_score := s.score temp2,
    
@pre_course_id := s.course_id temp3
    
FROM score s, (SELECT @cur_rank := 0, @pre_course_id := NULL, @pre_score := NULL, @rank_counter := 1)r
    
ORDER BY s.course_id, s.score DESC;

    结果如图：

　　　　

4.3 分组并列连续排名

  1. 使用DENSE_RANK实现：

    SELECT course_id, score,
    
DENSE_RANK() OVER(PARTITION BY course_id ORDER BY score DESC)
    
FROM score;
  1. 使用变量和IF语句实现：

    SELECT s.course_id, s.score,
    
IF(@pre_course_id = s.course_id,
    
   IF(@pre_score = s.score, @cur_rank, @cur_rank := @cur_rank + 1),
    
   @cur_rank := 1) ranking,
    
@pre_score := s.score,
    
@pre_course_id := s.course_id
    
FROM score s, (SELECT @cur_rank :=0, @pre_score = NULL, @pre_course_id := NULL) r
    
ORDER BY course_id, score DESC;

    可以将上述的IF条件提取出来：

    SELECT s.course_id, s.score,
    
IF(@pre_score = s.score, @cur_rank, @cur_rank := @cur_rank + 1) temp1,
    
@pre_score := s.score temp2,
    
IF(@pre_course_id = s.course_id, @cur_rank, @cur_rank := 1) ranking,
    
@pre_course_id := s.course_id
    
FROM score s, (SELECT @cur_rank :=0, @pre_score = NULL, @pre_course_id := NULL) r
    
ORDER BY course_id, score DESC;

    结果如图：

　　　　

最新文章

  1. Python正则表达式汇总
  2. mod mono xsp
  3. asp.net跨页面传值
  4. 添加hive默认配置hiverc
  5. BJUI 转
  6. 树莓派 wheezy安装与远程登录配置
  7. C# 委托的理解
  8. vue 购物车练习
  9. linux下面的打包压缩命令
  10. 使用伪类before和after
  11. u-boot移植（十三）---代码修改---裁剪及环境变量 二
  12. Axure RP 8 下载 激活可以使用的授权码、用户名、秘钥等
  13. celery 4.1下报kombu.exceptions.EncodeError: Object of type 'bytes' is not JSON serializable 处理方式
  14. [转载]windows下安装Python虚拟环境virtualenv,virtualenvwrapper-win
  15. HTML5/CSS3基础
  16. nuget.org 无法加载源 https://api.nuget.org/v3/index.json 的服务索引
  17. getViewTreeObserver
  18. Hive开窗函数的理解
  19. 操作系统-服务器-百科：Windows Server
  20. 关于div设置display: inline-block之后盒子之间间距的处理

热门文章

  1. 开启MySQL数据库远程连接
  2. 基于北斗gps设计的NTP网络时间服务器
  3. 导出SQL SERVER 数据字典语句
  4. 搭建Redis高可用集群的哨兵模式（Redis-Sentinel）【Windows环境】
  5. git 提示 fatal: Authentication failed for....
  6. FTP主动模式（Port）和被动模式（Passive）的区别
  7. （0502）《UVM》sequence
  8. （0524） rbf 格式 （intel）
  9. mysql 的 json 类型
  10. OpenJ_Bailian - 1088