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

select  dept_no, emp_no, salary as maxSalary 
 from(
    select 
        dept_no, 
        emp_no, 
        salary, 
        row_number()over(partition by dept_no order by salary desc) ranking 
    from dept_emp left join salaries using(emp_no)
 ) as a 
where ranking = 1;

第一步，按部门排序

第二部，选取ranking=1（即salary最高）对应的行

过程在

https://dbfiddle.uk/GlOlW-jg