一、问题描述

二、基本思路

思路1:使用窗口函数给每个部门员工薪水排名好后作为子表筛选
思路2:将两张表连接后,对dept_no和salary进行筛选(用where子查询进行筛选) 

三、完成代码

#思路1
select dept_no,s.emp_no,s.salary from(
select dept_no,salaries.emp_no emp_no,salary,dense_rank()over(partition by dept_no order by salary desc) r
from dept_emp left join salaries on dept_emp.emp_no =salaries.emp_no
order by salary desc) s
where s.r = 1
order by dept_no asc

#思路2
select dept_no,s.emp_no,salary maxSalary
from dept_emp de join salaries s on de.emp_no=s.emp_no 
where (dept_no,salary) in (select dept_no,max(salary)
                              from dept_emp de join salaries s 
               on de.emp_no=s.emp_no 
               where s.to_date='9999-01-01' and de.to_date='9999-01-01' 
               group by dept_no)
order by dept_no