前言

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('2020001','mysql',90),('2020001','C++',85),('2020003','English',100),('2020002','mysql',50),('2020002','C++',70),('2020002','English',99);Query OK, 6 rows affected (0.00 sec)Records: 6 Duplicates: 0 Warnings: 0mysql> insert into tb_score(stu_no,course,score)values('2020003','mysql',78),('2020003','C++',81),('2020003','English',80),('2020004','mysql',80),('2020004','C++',60),('2020004','English',100);Query OK, 6 rows affected (0.01 sec)Records: 6 Duplicates: 0 Warnings: 0mysql> insert into tb_score(stu_no,course,score)values('2020005','mysql',98),('2020005','C++',96),('2020005','English',70),('2020006','mysql',60),('2020006','C++',90),('2020006','English',70);Query OK, 6 rows affected (0.01 sec)Records: 6 Duplicates: 0 Warnings: 0mysql> insert into tb_score(stu_no,course,score)values('2020007','mysql',50),('2020007','C++',66),('2020007','English',76),('2020008','mysql',90),('2020008','C++',69),('2020008','English',86);Query OK, 6 rows affected (0.01 sec)Records: 6 Duplicates: 0 Warnings: 0mysql> insert into tb_score(stu_no,course,score)values('2020009','mysql',70),('2020009','C++',66),('2020009','English',86),('2020010','mysql',75),('2020010','C++',76),('2020010','English',81);Query OK, 6 rows affected (0.01 sec)Records: 6 Duplicates: 0 Warnings: 0mysql> insert into tb_score(stu_no,course,score)values('2020011','mysql',90),('2020012','C++',85),('2020011','English',84),('2020012','English',75),('2020013','C++',96),('2020013','English',88);Query OK, 6 rows affected (0.01 sec)Records: 6 Duplicates: 0 Warnings: 0

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

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)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函数，结果如下：

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)

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)

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. ES6 变量声明，箭头函数，数组方法，解构赋值，JSON，类与继承，模块化练习
  2. 箭头函数的基础使用
  3. Python技巧匿名函数、回调函数和高阶函数
  4. 浅析android通过jni控制service服务程序的简易流程
  5. Android官方入门文档[1]创建一个Android项目
  6. Android(安卓)bluetooth介绍（四）： a2dp connect流程分析
  7. Android架构分析之使用自定义硬件抽象层(HAL)模块
  8. Android中OpenMax的适配层
  9. android 包管理系统分析

随机推荐

  1. Android一些经常涉及到的权限【转】
  2. This text field does not specify an in
  3. android wrapper C调用java api
  4. Android跨进程通信IPC之11——Binder驱动
  5. android上传图片至服务器
  6. Android导出Kml
  7. minSdkVersion，targetSdkVersion，maxSdkVer
  8. Android开发学习---使用Intelij idea 13.
  9. android画图——Path()的使用
  10. 坑爹的android碎片化