题解 | #获取每个部门中当前员工薪水最高的相关信息#
获取每个部门中当前员工薪水最高的相关信息
https://www.nowcoder.com/practice/4a052e3e1df5435880d4353eb18a91c6
方法一：聚合函数
with t1 as(
select dept_emp.dept_no,dept_emp.emp_no,salaries.salary
from salaries
join dept_emp using(emp_no)
where salaries.to_date="9999-01-01"
and dept_emp.to_date="9999-01-01"
)
select *
from t1
where (t1.dept_no,t1.salary) in (
select dept_no,max(salary)
from t1
group by dept_no
)
方法二：窗口函数
select t1.dept_no,t1.emp_no,t1.ms
from (
select dept_emp.emp_no
,dept_emp.dept_no
,salaries.salary
,max(salary)over(partition by dept_no ) as ms
from dept_emp
join salaries using(emp_no)
where dept_emp.to_date="9999-01-01"
and salaries.to_date="9999-01-01")t1
where t1.salary=t1.ms
