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

# 2.当s_rank等于1时，则salary最大
select 
    dept_no,
    emp_no,
    salary as maxSalary
from
(
# 1.用开窗排序函数RANK()对salary进行一个排序，生成s_rank列
    select 
        dept_no,
        a.emp_no,
        salary,
	    (rank() over(partition by dept_no order by salary desc)) as s_rank
	from 
        dept_emp as a 
    inner join 
        salaries as b 
    on 
        a.emp_no = b.emp_no
    order by 
        dept_no
) as c
where 
    c.s_rank = 1;