select dept_no
, s.emp_no
, salary maxSalary
from
(select dept_no
, d.emp_no
, salary maxSalary
,rank() over(partition by dept_no order by salary desc) new
from dept_emp d
join salaries s
on d.emp_no=s.emp_no and d.to_date = '9999-01-01' and s.to_date='9999-01-01'
) as rk
join salaries s
on rk.emp_no=s.emp_no 
where rk.new = 1
order by dept_no asc

用子查询解决问题，欢迎大家讨论