MySQL8.0之前，做数据排名统计等相当痛苦，因为没有像Oracle、SQL SERVER 、PostgreSQL等其他数据库那样的窗口函数。但随着MySQL8.0中新增了窗口函数之后，针对这类统计就再也不是事了，本文就以常用的排序实例介绍MySQL的窗口函数。

1、准备工作

创建表及测试数据

mysql> use testdb;

Database changed

/*  创建表 */

mysql> create  table tb_score(id int primary key auto_increment,stu_no varchar(10),course varchar(50),score decimal(4,1)，key idx_stuNo_course(stu_no,course));

Query OK, 0 rows affected (0.03 sec)

mysql> show tables;

+------------------+

| Tables_in_testdb |

+------------------+

| tb_score         |

+------------------+

/* 新增一批测试数据 */

mysql> insert into tb_score(stu_no,course,score)values('','mysql',90),('','C++',85),('','English',100),('','mysql',50),('','C++',70),('','English',99);

Query OK, 6 rows affected (0.00 sec)

Records: 6  Duplicates: 0  Warnings: 0

mysql> insert into tb_score(stu_no,course,score)values('','mysql',78),('','C++',81),('','English',80),('','mysql',80),('','C++',60),('','English',100);

Query OK, 6 rows affected (0.01 sec)

Records: 6  Duplicates: 0  Warnings: 0

mysql> insert into tb_score(stu_no,course,score)values('','mysql',98),('','C++',96),('','English',70),('','mysql',60),('','C++',90),('','English',70);

Query OK, 6 rows affected (0.01 sec)

Records: 6  Duplicates: 0  Warnings: 0

mysql> insert into tb_score(stu_no,course,score)values('','mysql',50),('','C++',66),('','English',76),('','mysql',90),('','C++',69),('','English',86);

Query OK, 6 rows affected (0.01 sec)

Records: 6  Duplicates: 0  Warnings: 0

mysql> insert into tb_score(stu_no,course,score)values('','mysql',70),('','C++',66),('','English',86),('','mysql',75),('','C++',76),('','English',81);

Query OK, 6 rows affected (0.01 sec)

Records: 6  Duplicates: 0  Warnings: 0

mysql> insert into tb_score(stu_no,course,score)values('','mysql',90),('','C++',85),('','English',84),('','English',75),('','C++',96),('','English',88);

Query OK, 6 rows affected (0.01 sec)

Records: 6  Duplicates: 0  Warnings: 0

2、统计每门课程分数的排名

根据每门课程的分数从高到低进行排名，此时，会出现分数相同时怎么处理的问题，下面就根据不同的窗口函数来处理不同场景的需求

ROW_NUMBER

由结果可以看出，分数相同时按照学号顺序进行排名

mysql> select stu_no,course,score, row_number()over(partition by course order by score desc ) rn

    -> from tb_score;

+---------+---------+-------+----+

| stu_no  | course  | score | rn |

+---------+---------+-------+----+

| 2020005 | C++     |  96.0 |  1 |

| 2020013 | C++     |  96.0 |  2 |

| 2020006 | C++     |  90.0 |  3 |

| 2020001 | C++     |  85.0 |  4 |

| 2020012 | C++     |  85.0 |  5 |

| 2020003 | C++     |  81.0 |  6 |

| 2020010 | C++     |  76.0 |  7 |

| 2020002 | C++     |  70.0 |  8 |

| 2020008 | C++     |  69.0 |  9 |

| 2020007 | C++     |  66.0 | 10 |

| 2020009 | C++     |  66.0 | 11 |

| 2020004 | C++     |  60.0 | 12 |

| 2020003 | English | 100.0 |  1 |

| 2020004 | English | 100.0 |  2 |

| 2020002 | English |  99.0 |  3 |

| 2020013 | English |  88.0 |  4 |

| 2020008 | English |  86.0 |  5 |

| 2020009 | English |  86.0 |  6 |

| 2020011 | English |  84.0 |  7 |

| 2020010 | English |  81.0 |  8 |

| 2020003 | English |  80.0 |  9 |

| 2020007 | English |  76.0 | 10 |

| 2020012 | English |  75.0 | 11 |

| 2020005 | English |  70.0 | 12 |

| 2020006 | English |  70.0 | 13 |

| 2020005 | mysql   |  98.0 |  1 |

| 2020001 | mysql   |  90.0 |  2 |

| 2020008 | mysql   |  90.0 |  3 |

| 2020011 | mysql   |  90.0 |  4 |

| 2020004 | mysql   |  80.0 |  5 |

| 2020003 | mysql   |  78.0 |  6 |

| 2020010 | mysql   |  75.0 |  7 |

| 2020009 | mysql   |  70.0 |  8 |

| 2020006 | mysql   |  60.0 |  9 |

| 2020002 | mysql   |  50.0 | 10 |

| 2020007 | mysql   |  50.0 | 11 |

+---------+---------+-------+----+

36 rows in set (0.00 sec)

DENSE_RANK

为了让分数相同时排名也相同，则可以使用DENSE_RANK函数，结果如下：

mysql> select stu_no,course,score, DENSE_RANK()over(partition by course order by score desc ) rn

    -> from  tb_score  ;

+---------+---------+-------+----+

| stu_no  | course  | score | rn |

+---------+---------+-------+----+

| 2020005 | C++     |  96.0 |  1 |

| 2020013 | C++     |  96.0 |  1 |

| 2020006 | C++     |  90.0 |  2 |

| 2020001 | C++     |  85.0 |  3 |

| 2020012 | C++     |  85.0 |  3 |

| 2020003 | C++     |  81.0 |  4 |

| 2020010 | C++     |  76.0 |  5 |

| 2020002 | C++     |  70.0 |  6 |

| 2020008 | C++     |  69.0 |  7 |

| 2020007 | C++     |  66.0 |  8 |

| 2020009 | C++     |  66.0 |  8 |

| 2020004 | C++     |  60.0 |  9 |

| 2020003 | English | 100.0 |  1 |

| 2020004 | English | 100.0 |  1 |

| 2020002 | English |  99.0 |  2 |

| 2020013 | English |  88.0 |  3 |

| 2020008 | English |  86.0 |  4 |

| 2020009 | English |  86.0 |  4 |

| 2020011 | English |  84.0 |  5 |

| 2020010 | English |  81.0 |  6 |

| 2020003 | English |  80.0 |  7 |

| 2020007 | English |  76.0 |  8 |

| 2020012 | English |  75.0 |  9 |

| 2020005 | English |  70.0 | 10 |

| 2020006 | English |  70.0 | 10 |

| 2020005 | mysql   |  98.0 |  1 |

| 2020001 | mysql   |  90.0 |  2 |

| 2020008 | mysql   |  90.0 |  2 |

| 2020011 | mysql   |  90.0 |  2 |

| 2020004 | mysql   |  80.0 |  3 |

| 2020003 | mysql   |  78.0 |  4 |

| 2020010 | mysql   |  75.0 |  5 |

| 2020009 | mysql   |  70.0 |  6 |

| 2020006 | mysql   |  60.0 |  7 |

| 2020002 | mysql   |  50.0 |  8 |

| 2020007 | mysql   |  50.0 |  8 |

+---------+---------+-------+----+

36 rows in set (0.00 sec)

RANK

DENSE_RANK的结果是分数相同时排名相同了，但是下一个名次是紧接着上一个名次的，如果2个并列的第1之后，下一个我想是第3名，则可以使用RANK函数实现

mysql> select stu_no,course,score, rank()over(partition by course order by score desc ) rn

    -> from  tb_score;

+---------+---------+-------+----+

| stu_no  | course  | score | rn |

+---------+---------+-------+----+

| 2020005 | C++     |  96.0 |  1 |

| 2020013 | C++     |  96.0 |  1 |

| 2020006 | C++     |  90.0 |  3 |

| 2020001 | C++     |  85.0 |  4 |

| 2020012 | C++     |  85.0 |  4 |

| 2020003 | C++     |  81.0 |  6 |

| 2020010 | C++     |  76.0 |  7 |

| 2020002 | C++     |  70.0 |  8 |

| 2020008 | C++     |  69.0 |  9 |

| 2020007 | C++     |  66.0 | 10 |

| 2020009 | C++     |  66.0 | 10 |

| 2020004 | C++     |  60.0 | 12 |

| 2020003 | English | 100.0 |  1 |

| 2020004 | English | 100.0 |  1 |

| 2020002 | English |  99.0 |  3 |

| 2020013 | English |  88.0 |  4 |

| 2020008 | English |  86.0 |  5 |

| 2020009 | English |  86.0 |  5 |

| 2020011 | English |  84.0 |  7 |

| 2020010 | English |  81.0 |  8 |

| 2020003 | English |  80.0 |  9 |

| 2020007 | English |  76.0 | 10 |

| 2020012 | English |  75.0 | 11 |

| 2020005 | English |  70.0 | 12 |

| 2020006 | English |  70.0 | 12 |

| 2020005 | mysql   |  98.0 |  1 |

| 2020001 | mysql   |  90.0 |  2 |

| 2020008 | mysql   |  90.0 |  2 |

| 2020011 | mysql   |  90.0 |  2 |

| 2020004 | mysql   |  80.0 |  5 |

| 2020003 | mysql   |  78.0 |  6 |

| 2020010 | mysql   |  75.0 |  7 |

| 2020009 | mysql   |  70.0 |  8 |

| 2020006 | mysql   |  60.0 |  9 |

| 2020002 | mysql   |  50.0 | 10 |

| 2020007 | mysql   |  50.0 | 10 |

+---------+---------+-------+----+

36 rows in set (0.01 sec)

这样就实现了各种排序需求。

NTILE

NTILE函数的作用是对每个分组排名后，再将对应分组分成N个小组，例如

mysql> select stu_no,course,score, rank()over(partition by course order by score desc )rn,NTILE(2)over(partition by course order by score desc ) rn_group  from  tb_score;

+---------+---------+-------+----+----------+

| stu_no  | course  | score | rn | rn_group |

+---------+---------+-------+----+----------+

| 2020005 | C++     |  96.0 |  1 |        1 |

| 2020013 | C++     |  96.0 |  1 |        1 |

| 2020006 | C++     |  90.0 |  3 |        1 |

| 2020001 | C++     |  85.0 |  4 |        1 |

| 2020012 | C++     |  85.0 |  4 |        1 |

| 2020003 | C++     |  81.0 |  6 |        1 |

| 2020010 | C++     |  76.0 |  7 |        2 |

| 2020002 | C++     |  70.0 |  8 |        2 |

| 2020008 | C++     |  69.0 |  9 |        2 |

| 2020007 | C++     |  66.0 | 10 |        2 |

| 2020009 | C++     |  66.0 | 10 |        2 |

| 2020004 | C++     |  60.0 | 12 |        2 |

| 2020003 | English | 100.0 |  1 |        1 |

| 2020004 | English | 100.0 |  1 |        1 |

| 2020002 | English |  99.0 |  3 |        1 |

| 2020013 | English |  88.0 |  4 |        1 |

| 2020008 | English |  86.0 |  5 |        1 |

| 2020009 | English |  86.0 |  5 |        1 |

| 2020011 | English |  84.0 |  7 |        1 |

| 2020010 | English |  81.0 |  8 |        2 |

| 2020003 | English |  80.0 |  9 |        2 |

| 2020007 | English |  76.0 | 10 |        2 |

| 2020012 | English |  75.0 | 11 |        2 |

| 2020005 | English |  70.0 | 12 |        2 |

| 2020006 | English |  70.0 | 12 |        2 |

| 2020005 | mysql   |  98.0 |  1 |        1 |

| 2020001 | mysql   |  90.0 |  2 |        1 |

| 2020008 | mysql   |  90.0 |  2 |        1 |

| 2020011 | mysql   |  90.0 |  2 |        1 |

| 2020004 | mysql   |  80.0 |  5 |        1 |

| 2020003 | mysql   |  78.0 |  6 |        1 |

| 2020010 | mysql   |  75.0 |  7 |        2 |

| 2020009 | mysql   |  70.0 |  8 |        2 |

| 2020006 | mysql   |  60.0 |  9 |        2 |

| 2020002 | mysql   |  50.0 | 10 |        2 |

| 2020007 | mysql   |  50.0 | 10 |        2 |

+---------+---------+-------+----+----------+

36 rows in set (0.01 sec)

3、窗口函数小结

MySQL中还有许多其他的窗口函数，本文列举一些，大家可以自行测试

 
类别 函数 说明
排序 ROW_NUMBER 为表中的每一行分配一个序号，可以指定分组（也可以不指定）及排序字段
DENSE_RANK 根据排序字段为每个分组中的每一行分配一个序号。 排名值相同时，序号相同，序号中没有间隙（1,1,2,3这种）
RANK 根据排序字段为每个分组中的每一行分配一个序号。 排名值相同时，序号相同，但序号中存在间隙（1,1,3,4这种）
NTILE 根据排序字段为每个分组中根据指定字段的排序再分成对应的组
分布 PERCENT_RANK 计算各分组或结果集中行的百分数等级
CUME_DIST 计算某个值在一组有序的数据中累计的分布
前后 LEAD 返回分组中当前行之后的第N行的值。如果不存在对应行，则返回NULL。比如N=1时，第一名对应的值是第二名的，最后一名结果是NULL
LAG 返回分组中当前行之前的第N行的值。如果不存在对应行，则返回NULL。比如N=1时，第一名对应的值是是NUL，最后一名结果是倒数第2的值
首尾中 FIRST_VALUE 返回每个分组中第一名对应的字段（或表达式）的值，例如本文中可以是第一名的分数、学号等任意字段的值
LAST_VALUE 返回每个分组中最后一名对应的字段（或表达式）的值，例如本文中可以是最后一名的分数、学号等任意字段的值
NTH_VALUE

返回每个分组中排名第N的对应字段（或表达式）的值，但小于N的行对应的值是NULL

MySQL中主要的窗口函数先总结这么多，建议还是得动手实践一番。另外，MySQL5.7及之前版本的排序方式的实现很多人已总结，也建议实操一番。

想了解更多内容或参与技术交流可以关注微信公众号【数据库干货铺】或进技术交流群沟通。

最新文章

  1. CodeForces Round 197 Div2
  2. Valid format values for declare-styleable/attr tags[转]
  3. shodan
  4. ural1424 Minibus
  5. mysql按某个列执行1-N排名的问题
  6. Solr集群搭建详细教程（二）
  7. 面向复杂应用，Node.js中的IoC容器 -- Rockerjs/core
  8. h5的改进:
  9. VB.NET获取系统特殊目录
  10. 初识kafka
  11. Linux Oracle bash: “sqlplus / as sysdba”: command not found 解决方法
  12. day063 form 和modelform组件
  13. VMware Ubuntu 虚拟机安装 VMwareTools （VMware虚拟机如何与主机互相复制文件）
  14. 一、变量.二、过滤器(filter).三、标签(tag).四、条件分支tag.五、迭代器tag.六、自定义过滤器与标签.七、全系统过滤器（了解）
  15. 第2章 如何安装KEIL5
  16. 思考的乐趣----matrix67数学笔记：最精妙的无字证明
  17. 通过phoenix在hbase上创建二级索引，Secondary Indexing
  18. artTemplate 模板使用
  19. cnblog博客管理
  20. spring-quartz 定时器 给targetMethod传递参数

热门文章

  1. 动态ip服务器 动态ip服务器的常用连接方式 收藏版
  2. 【算法】单元最短路径之Bellman-Ford算法和SPFA算法
  3. 【Hadoop】hdfs,剖析文件上传
  4. git可视化打包更新文件
  5. CVE-2020-0796永恒之黑复现POC EXP以及修复方案
  6. （Java实现） 数塔问题
  7. Java实现 LeetCode 173 二叉搜索树迭代器
  8. java实现棋盘上的麦子
  9. java实现第七届蓝桥杯机器人塔
  10. 连接mongoodb并且向数据库添加信息