题解 | #获取每个部门中当前员工薪水最高的相关信息#

select dept_no,emp_no,maxSalary from
(select dept_no,emp_no,salary as maxSalary,rank()over(partition by dept_no order by salary desc) as the_rank from
(select dept_no,salaries.emp_no,salary from dept_emp join salaries on dept_emp.emp_no=salaries.emp_no) as num1)as num2 where num2.the_rank=1 order by dept_no

select dept_no,emp_no,salary as maxSalary from
(
select de.dept_no,de.emp_no,s.salary,rank()over(partition by de.dept_no order by s.salary desc) as the_rank from  dept_emp de join salaries s on de.emp_no=s.emp_no
) num1 where num1.the_rank=1 order by dept_no