SQL今日一题（11）：窗口函数
题目描述
获取所有部门中当前(dept_emp.to_date = '9999-01-01')员工当前(salaries.to_date='9999-01-01')薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary
这题用到dept_emp表和salaries表，以emp_no为公共字段
dept_emp表
salaries表
方法1
select d.dept_no ,d.emp_no ,max(s.salary) as salary from dept_emp as d,salaries as swhere d.emp_no = s.emp_no and d.to_date = '9999-01-01' and s.to_date = '9999-01-01'group by d.dept_no,d.emp_no
通常的想法就是这样写了，max函数取最大值再对d.dept_no和d.emp_no分组，结果如下图所示，但其实这样取出来是不符合结果的，因为题目要求一个部门只有一个薪水的最大值，但因为我们对emp_no也分组了，所以会取出来一个部门下不同员工的salary。
方法2
select r.dept_no ,r.emp_no ,max(r.salary) from ( select d.dept_no ,d.emp_no ,s.salary from dept_emp d,salaries s where d.emp_no=s.emp_no and d.to_date='9999-01-01' and s.to_date='9999-01-01' order by s.salary desc)as rgroup by r.dept_noorder by r.dept_no asc
这道题网上有很多种解答方法，这种解法是牛客网上讨论最多的一种。
1、首先将两个表连接起来按salary降序，where子句给出限定条件“当前”日期和公共字段的连接，将连接后的表命名为r表
2、再将r表按照dept_no分组，并从中取出最大的salary
这是只对dept_no分组，但细琢磨这是不对的，因为按照语法，group by 后要跟select以后聚合函数前的所有字段，也就是说select dept_no,emp_no了以后，group by 就必须包含dept_no和emp_no。
有的数据库可以只对dept_no分组，但返回的emp_no是默认取第一条，这里第一条不一定是最大的salary对应的那条，因此要先对salary降序排列，而根据语法，order by 要写在group by的后面，所以用了子查询。
而有的数据库group by会很严格的报错，所以要如何绕过emp_no分组呢？
方法3
select rank.dept_no ,rank.emp_no ,rank.salary from( select d.dept_no ,d.emp_no ,s.salary ,ROW_NUMBER() over( partition by d.dept_no order by s.salary desc ) as nums from dept_emp d,salaries s where d.emp_no=s.emp_no and d.to_date='9999-01-01' and s.to_date='9999-01-01') rankwhere rank.nums = 1
其实对方法1中的结果我们只要再增加一列排序就可以了啊，对每一个分组降序排列，取每个排序为1的不就符合题目要求了吗，实现的方法就要用到窗口函数里的row_number() over()了。
1、首先将dept_emp和salaries两个表以公共字段emp_no连接起来，并用where子句限制“当前”条件
2、同时加入row_number() over(partition by d.dept_no order by s.salary)语句，意思是按照dept_no分组，在每一个dept_no的分组内按照salary降序排列，排序之后，对每一个分组内的多行数据，标记上序号，序号从1开始，依次递增，同时也给序号赋值为nums
3、进行开窗操作后，再进行一次select+where的操作，来选出需要的数据，即nums = 1的记录。
以上3种方法均能通过牛客网的练习，但能通过不代表就是对的，这道题目很值得思考，尤其是对group by和窗口函数掌握不深入的小伙伴来说，看一眼就过了，以为自己明白了，但其实还差得多，希望大家好好思考。
知识点
max函数
返回指定列中的最大值
要指定列名，如max(column)
row_number
语法：row_number() over(partition by column1,column2 order by column3) as column4
按照column1,column2分组，对每个组内按照column3排序，并将排序的序号命名为column4
赋予唯一的连续位次，如有相同的3条记录排名为1时，结果记为1,2,3
更多相关文章
- 为什么说 Python 内置函数并不是万能的？
- 给你的Excel增加正则处理函数，简直如虎添翼
- Python 之父为什么嫌弃 lambda 匿名函数？
- Python 函数为什么会默认返回 None？
- Python 为什么没有 main 函数？为什么我不推荐写 main 函数？
- 学编程这么久，还傻傻分不清什么是方法（method），什么是函数（function）？
- 秒懂！图解四个实用的Pandas函数！
- 一个真实问题，搞定三个冷门pandas函数
- 给女朋友讲了讲 V8 引擎的“回调函数”！