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

select t1.dept_no,emp_no,maxSalary from (
    select dept_no,max(salary) maxSalary from dept_emp de left join salaries s on de.emp_no=s.emp_no
group by dept_no
order by dept_no
) as t1
right join 
(select s.emp_no,salary,dept_no from dept_emp de right join salaries s on de.emp_no=s.emp_no)as t2
on t2.dept_no=t1.dept_no
where t2.salary=t1.maxSalary
order by t1.dept_no
;

1.求出每个部门的最高薪水：

select dept_no,max(salary) maxSalary from dept_emp de left join salaries s on de.emp_no=s.emp_no

group by dept_no

order by dept_no

) as t1

2.每个部门每位员工的薪水：

(select s.emp_no,salary,dept_no from dept_emp de right join salaries s on de.emp_no=s.emp_no)as t2

3.将员工薪水表t2与部门最高薪水表t1连接，因可能存在同一部门最高薪员工有多名的情况，故需将t2右连接t1

并根据条件where t2.salary=t1.maxSalary筛选出部门薪水最高的员工