MySQL8.0窗口函数入门实践及总结

前言

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: 0
mysql> 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: 0
mysql> 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: 0
mysql> 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: 0
mysql> 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: 0
mysql> 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

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)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 | Engl<i>本文来源gaodai$ma#com搞$代*码*网</i>ish | 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)

搞代码网（gaodaima.com）提供的所有资源部分来自互联网，如果有侵犯您的版权或其他权益，请说明详细缘由并提供版权或权益证明然后发送到邮箱[email protected]‍，我们会在看到邮件的第一时间内为您处理，或直接联系QQ：872152909。本网站采用BY-NC-SA协议进行授权
转载请注明原文链接：MySQL8.0窗口函数入门实践及总结

Hi，您需要填写昵称和邮箱！