使用窗口函数求解

select d.dept_no, d.emp_no, d.salary
from
(select c.dept_no,c.emp_no,c.salary,
row_number() over(partition by c.dept_no order by c.salary desc) as ranking
from
         (select a.dept_no,a.emp_no,b.salary
          from dept_emp as a inner join salaries as b on a.emp_no = b.emp_no) c
    
)d

where d.ranking = 1
order by d.dept_no;