SQL今日一题（11）：窗口函数_窗函数 cola条件-优快云博客

本文链接：https://blog.youkuaiyun.com/data_cola/article/details/106773143

本文是SQL每日一题的第11题，讲解如何获取部门中当前薪水最高的员工信息。通过三种方法解析，涉及max函数、分组原则及窗口函数row_number()的使用。通过对数据表dept_emp和salaries的连接与操作，揭示了在不同数据库环境中处理分组和排序的策略。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

这是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 s
where 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 r
group by r.dept_no
order 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') rank
where 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的记录。