题解 | #获取每个部门中当前员工薪水最高的相关信息#
获取每个部门中当前员工薪水最高的相关信息
https://www.nowcoder.com/practice/4a052e3e1df5435880d4353eb18a91c6
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筛选出部门薪水最高的员工
