开窗函数

更多分享请关注我的“成集锦”。

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

在SQL中，我们经常会遇到一个需求:分组排序、分组求和等需求。如果在Oracle、SQLserver、postgresql等数据库中可以轻松实现这样的需求，那么一个开窗函数row _ number()over(partition by XXX，yyy order by zzz)就可以解决。

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

准备实验环境准备建表语句

CREATE TABLE ` EMP `( ` id ` int(11)NOT NULL，` EMP _ name ` varchar(255)DEFAULT NULL，` dept _ no ` varchar(255)DEFAULT NULL，` emp_salary` int(10) DEFAULT NULL，` emp_hire_date` date DEFAULT NULL，PRIMARY KEY(` id `))ENGINE = InnoDB DEFAULT CHARSET = utf8mb 4；初始化数据插入到` mysql _ db` .`emp` (`id `，` emp _ name `，` dept _ no `，` emp _ salary `，` emp _ hire _ date `)值(1，& # 39；张三& # 39；, '0001', 5000, '2017-01-11');插入` mysql_db`.`emp`(`id `，` emp_name `，` dept_no `，` emp_salary `，` emp_hire_date `)值(2，& # 39；李四& # 39；, '0002', 1000, '2018-10-10');插入` mysql_db`.`emp`(`id `，` emp_name `，` dept_no `，` emp_salary `，` emp_hire_date `)值(3，& # 39；王武& # 39；, '0003', 2000, '2018-12-19');插入` mysql_db`.`emp`(`id `，` emp_name `，` dept_no `，` emp_salary `，` emp_hire_date `)值(4，& # 39；刘钊& # 39；, '0002', 4000, '2019-09-11');插入` mysql_db`.`emp`(`id `，` emp_name `，` dept_no `，` emp_salary `，` emp_hire_date `)值(5，& # 39；王& # 39；, '0001', 3000, '2019-03-14');插入` mysql_db`.`emp`(`id `，` emp_name `，` dept_no `，` emp_salary `，` emp_hire_date `)值(6，& # 39；刘洋& # 39；, '0002', 6000, '2019-08-08');插入` mysql_db`.`emp`(`id `，` emp_name `，` dept_no `，` emp_salary `，` emp_hire_date `)值(7，& # 39；周心怡& # 39；, '0003', 500, '2015-06-10');插入` mysql_db`.`emp`(`id `，` emp_name `，` dept_no `，` emp_salary `，` emp_hire_date `)值(8，& # 39；毛致宇& # 39；, '0004', 4500, '2016-09-20');插入` mysql_db`.`emp`(`id `，` emp_name `，` dept_no `，` emp_salary `，` emp_hire_date `)值(9，& # 39；刘德仁& # 39；, '0002', 3500, '2016-02-25');插入` mysql_db`.`emp`(`id `，` emp_name `，` dept_no `，` emp_salary `，` emp_hire_date `)值(10，& # 39；范& # 39；, '0001', 3000, '2020-02-12');插入` mysql_db`.`emp`(`id `，` emp_name `，` dept_no `，` emp_salary `，` emp_hire_date `)值(11，& # 39；婷婷梅& # 39；, '0005', 8000, '2013-07-07');插入` mysql_db`.`emp`(`id `，` emp_name `，` dept_no `，` emp_salary `，` emp_hire_date `)值(12，& # 39；邴正& # 39；, '0005', 1000, '2014-11-17');最终的环境如下:

分组排序

需求描述:获取各部门薪酬最高员工的信息。

需求分析:

按照部门分组，每一部门的员工放在一组，然后基于这个组中的数据，按照工资降序排列。然后再根据排序后的结果集，获取排序为1的数据行即为结果。步骤1的SQL如下：定义两个变量，row_num_t用于存储每一个组中的排序结果。dept_no_t用于存储判断数据行是否是同一组。同事给他们分别初始化数据。然后再查询的字段当中，先判断一下当前是否和上一行的部门编号是同一个部门编号。如果是同一个部门编号，则将排序结果字段加1；如果不是同个部门编号，表示要切换为新的组了，这个时候，把排序结果字段重置为1。

selectcase when @dept_no_t != x.dept_no then @row_num_t := 1else @row_num_t := @row_num_t + 1end as 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 t2order by dept_no,emp_salary desc;步骤1的示例结果如下：步骤2的SQL语句如下：在步骤1的SQL基础之上，在外出包裹一层查询，然后时候用where条件获取上面内层查询结果中排序为1的数据行。

select * from (selectcase when @dept_no_t != x.dept_no then @row_num_t := 1else @row_num_t := @row_num_t + 1end as 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 t2order by dept_no,emp_salary desc) as y where y.sort_result = 1;步骤2的示例结果如下：分组求和

需求描述:累加各部门所有员工的工资总额。

解析:按照部门分组，把每个部门的员工放在一个组里，然后基于这个组里的数据，逐行累加这个部门所有员工的工资。

SQL如下：定义一个用于存储最后每组员工工资之和的变量emp_salary_sum_t，然后再每一行数据是否为同一组数据，如果是同一组数据，则将这行数据的工资，累加到工资之和的变量中；如果不是同一组数据，把当前行的工资赋值给每组工资之和的变量。

selectcase when @dept_no_t != x.dept_no then @row_num_t := 1else @row_num_t := @row_num_t + 1end as sort_result,case when @dept_no_t != x.dept_no then @emp_salary_sum_t := x.emp_salarywhen @dept_no_t = x.dept_no then @emp_salary_sum_t := @emp_salary_sum_t + x.emp_salaryend as 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 t3order by dept_no,emp_salary desc;最后的示例结果如下：分组求最大值

需求描述:计算每个员工与部门中薪资最高的员工之间的薪资差异。

需求分析:

根据员工的部门分组，然后判断得到每组数据中，工资最高的员工的工资。把这个作为一个新列查询出出来。基于步骤1的结果集中的新列，和员工的工资列做减法得到差值。步骤1SQL语句如下：

selectcase when @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_t then @emp_salary_max_t := x.emp_salaryelse @emp_salary_max_tend as 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 t4order by dept_no, emp_salary desc步骤1实验结果如下：步骤2SQL语句如下：

select y.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 (selectcase when @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_t then @emp_salary_max_t := x.emp_salaryelse @emp_salary_max_tend as 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 t4order by dept_no, emp_salary desc) as y;步骤2实验结果如下：

以上是MySQL5.7版本中如何使用开窗函数的例子。希望这篇文章能帮到你，也欢迎你分享给更多的朋友。有问题请留言，我一看到就回复你。