mysql8.0开窗函数_MySQL5.7中如何使用开窗函数

关注我，获的更多分享。

背景准备实验环境准备建表语句初始化数据分组排序分组统计分组求最大值背景

在SQL中我们经常遇到一种需求：分组排序，分组求和等各种需求。像这样的需求，如果在Oracle、SQLserver、postgresql等数据库中很容易实现，一个开窗函数row_nubmer() over(partition by xxx,yyy order by zzz)就可以解决。

但是在MySQL8.0版本之前，是没有这样的开窗函数的。好在8.0之后的版本已经内置了开窗函数。不必自己写实现逻辑了。但是我们目前还有很多人在使用5.7版本。那么在5.7版本中，如何实现开窗函数的功能呢？

准备实验环境

准备建表语句

CREATETABLE`emp`(`id`int(11)NOTNULL,`emp_name`varchar(255)DEFAULTNULL,`dept_no`varchar(255)DEFAULTNULL,`emp_salary`int(10)DEFAULTNULL,`emp_hire_date`dateDEFAULTNULL,PRIMARYKEY(`id`))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;

初始化数据

INSERTINTO`mysql_db`.`emp`(`id`,`emp_name`,`dept_no`,`emp_salary`,`emp_hire_date`)VALUES(1,'张三','0001',5000,'2017-01-11');INSERTINTO`mysql_db`.`emp`(`id`,`emp_name`,`dept_no`,`emp_salary`,`emp_hire_date`)VALUES(2,'李四','0002',1000,'2018-10-10');INSERTINTO`mysql_db`.`emp`(`id`,`emp_name`,`dept_no`,`emp_salary`,`emp_hire_date`)VALUES(3,'王五','0003',2000,'2018-12-19');INSERTINTO`mysql_db`.`emp`(`id`,`emp_name`,`dept_no`,`emp_salary`,`emp_hire_date`)VALUES(4,'赵六','0002',4000,'2019-09-11');INSERTINTO`mysql_db`.`emp`(`id`,`emp_name`,`dept_no`,`emp_salary`,`emp_hire_date`)VALUES(5,'王强强','0001',3000,'2019-03-14');INSERTINTO`mysql_db`.`emp`(`id`,`emp_name`,`dept_no`,`emp_salary`,`emp_hire_date`)VALUES(6,'刘阳','0002',6000,'2019-08-08');INSERTINTO`mysql_db`.`emp`(`id`,`emp_name`,`dept_no`,`emp_salary`,`emp_hire_date`)VALUES(7,'周心怡','0003',500,'2015-06-10');INSERTINTO`mysql_db`.`emp`(`id`,`emp_name`,`dept_no`,`emp_salary`,`emp_hire_date`)VALUES(8,'毛志宇','0004',4500,'2016-09-20');INSERTINTO`mysql_db`.`emp`(`id`,`emp_name`,`dept_no`,`emp_salary`,`emp_hire_date`)VALUES(9,'刘德仁','0002',3500,'2016-02-25');INSERTINTO`mysql_db`.`emp`(`id`,`emp_name`,`dept_no`,`emp_salary`,`emp_hire_date`)VALUES(10,'范德武','0001',3000,'2020-02-12');INSERTINTO`mysql_db`.`emp`(`id`,`emp_name`,`dept_no`,`emp_salary`,`emp_hire_date`)VALUES(11,'梅婷婷','0005',8000,'2013-07-07');INSERTINTO`mysql_db`.`emp`(`id`,`emp_name`,`dept_no`,`emp_salary`,`emp_hire_date`)VALUES(12,'郑冰','0005',1000,'2014-11-17');

最后的环境如下：

分组排序

需求描述：获取每一个部门薪水最高的员工的信息。

需求分析：

按照部门分组，每一部门的员工放在一组，然后基于这个组中的数据，按照工资降序排列。然后再根据排序后的结果集，获取排序为1的数据行即为结果。步骤1的SQL如下：定义两个变量，row_num_t用于存储每一个组中的排序结果。dept_no_t用于存储判断数据行是否是同一组。同事给他们分别初始化数据。然后再查询的字段当中，先判断一下当前是否和上一行的部门编号是同一个部门编号。如果是同一个部门编号，则将排序结果字段加1；如果不是同个部门编号，表示要切换为新的组了，这个时候，把排序结果字段重置为1。selectcasewhen@dept_no_t!= x.dept_no then@row_num_t :=1else@row_num_t :=@row_num_t+1endas sort_result,x.id, x.emp_name,-- x.dept_no,@dept_no_t := x.dept_no as dept_no,x.emp_salary,x.emp_hire_datefrom emp as x,(select@dept_no_t :='')as t1,(select@row_num_t :=0)as t2orderbydept_no,emp_salary desc;

步骤1的示例结果如下：

步骤2的SQL语句如下：在步骤1的SQL基础之上，在外出包裹一层查询，然后时候用where条件获取上面内层查询结果中排序为1的数据行。select*from(selectcasewhen@dept_no_t!= x.dept_no then@row_num_t :=1else@row_num_t :=@row_num_t+1endas sort_result,x.id, x.emp_name,-- x.dept_no,@dept_no_t := x.dept_no as dept_no,x.emp_salary,x.emp_hire_datefrom emp as x,(select@dept_no_t :='')as t1,(select@row_num_t :=0)as t2orderbydept_no,emp_salary desc)as y where y.sort_result =1;

步骤2的示例结果如下：

分组统计

需求描述：累计统计每一个部门下所有员工的工资之和。

分析：按照部门分组，每一部门的员工放在一组，然后基于这个组中的数据，逐行累加该部门下所有员工的工资。

SQL如下：定义一个用于存储最后每组员工工资之和的变量emp_salary_sum_t，然后再每一行数据是否为同一组数据，如果是同一组数据，则将这行数据的工资，累加到工资之和的变量中；如果不是同一组数据，把当前行的工资赋值给每组工资之和的变量。selectcasewhen@dept_no_t!= x.dept_no then@row_num_t :=1else@row_num_t :=@row_num_t+1endas sort_result,casewhen@dept_no_t!= x.dept_no then@emp_salary_sum_t := x.emp_salary when@dept_no_t= x.dept_no then@emp_salary_sum_t :=@emp_salary_sum_t+ x.emp_salary endas emp_salary_sum, x.id, x.emp_name,-- x.dept_no,@dept_no_t := x.dept_no as dept_no, x.emp_salary, x.emp_hire_datefrom emp as x,(select@dept_no_t :='')as t1,(select@row_num_t :=0)as t2,(select@emp_salary_sum_t :=0)as t3orderby dept_no, emp_salary desc;

最后示例结果如下：

分组求最大值

需求描述：计算每个员工和部门中工资最高员工的工资差。

需求分析：

根据员工的部门分组，然后判断得到每组数据中，工资最高的员工的工资。把这个作为一个新列查询出出来。基于步骤1的结果集中的新列，和员工的工资列做减法得到差值。步骤1SQL语句如下：selectcasewhen@dept_no_t!= x.dept_no then@emp_salary_max_t := x.emp_salarywhen@dept_no_t= x.dept_noand x.emp_salary >@emp_salary_max_tthen@emp_salary_max_t := x.emp_salaryelse@emp_salary_max_tendas emp_salary_max,x.id, x.emp_name,@dept_no_t := x.dept_no as dept_no,x.emp_salary, x.emp_hire_datefrom emp as x,(select@dept_no_t :='')as t1,(select@emp_salary_max_t :=0)as t4orderby dept_no, emp_salary desc

步骤1实验结果如下：

步骤2SQL语句如下：selecty.emp_salary_max, y.emp_salary_max - y.emp_salary as cha,y.id, y.emp_name, y.dept_no, y.emp_salary, y.emp_hire_datefrom(selectcasewhen@dept_no_t!= x.dept_no then@emp_salary_max_t := x.emp_salarywhen@dept_no_t= x.dept_no and x.emp_salary >@emp_salary_max_tthen@emp_salary_max_t := x.emp_salaryelse@emp_salary_max_tendas emp_salary_max,x.id, x.emp_name,@dept_no_t := x.dept_no as dept_no,x.emp_salary, x.emp_hire_datefrom emp as x,(select@dept_no_t :='')as t1,(select@emp_salary_max_t :=0)as t4orderby dept_no, emp_salary desc)as y;

步骤2实验结果如下：

以上就是MySQL5.7版本中，如何使用开窗函数的示例。希望能这篇文章能够帮到你，也还原你把文章分享给更多的朋友。如果有什么问题请留言，我看到后会第一时间恢复你。