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

-- 1. 通过emp_no对两表进行关联，在通过dept_no进行分组，求每组最高薪资
select
    d.dept_no,
    max(s.salary) salary
from
    dept_emp d
        join
    salaries s
    on
        d.emp_no = s.emp_no
group by d.dept_no;
-- 查询结果
d001,88958
d002,92527

-- 2. 再次通过emp_no对两表进行关联，查询dept_no, emp_no, salary
select
    t1.dept_no,
    t2.emp_no,
    t2.salary
from
    dept_emp t1,
    salaries t2
-- 查询结果
d002,10001,88958
d001,10001,88958
d001,10001,88958
d002,10002,72527
d001,10002,72527
d001,10002,72527
d002,10003,92527
d001,10003,92527
d001,10003,92527

-- 3. 以步骤1的结果为过滤条件，使用where对步骤2进行过滤
--    看看都是谁部门编号为d001/d002，薪资又为88958/92527
select 
    t1.dept_no,
    t2.emp_no,
    t2.salary
from dept_emp t1,
     salaries t2
where t1.emp_no = t2.emp_no
and (t1.dept_no, t2.salary) in 
(select
	d.dept_no,
	max(s.salary) salary
from
	dept_emp d
join
	salaries s
on
	d.emp_no = s.emp_no
group by d.dept_no)
order by t1.dept_no asc;
--查询结果
d001,10001,88958
d002,10003,92527

-- 或者使用窗口函数进行排序，排序时指定分组(在组内进行排序)，薪资降序，子查询过滤条件
select
    t1.dept_no,
    t1.emp_no,
    t1.salary
from (
select
b.dept_no,
a.emp_no,
a.salary,
dense_rank() over (partition by b.dept_no order by a.salary desc ) as dsnk
from salaries a
join dept_emp b
on a.emp_no = b.emp_no
) t1
where t1.dsnk = 1;