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

1.获取每个部门、每个员工对应薪资表t1
SELECT d.dept_no,s.emp_no,s.salary
FROM dept_emp d
join salaries s
on d.emp_no=s.emp_no;t1

2.获取每个部门最大薪资表t2
SELECT d.dept_no,max(salary) salary
FROM dept_emp d
join salaries s
on d.emp_no=s.emp_no
group by dept_no;t2

3.连接t1、t2，找到各部门薪资等于t2最大薪资的员工，并按部门编号升序排列
SELECT t1.dept_no,t1.emp_no,t2.salary
FROM
(SELECT d.dept_no,s.emp_no,s.salary
FROM dept_emp d
join salaries s
on d.emp_no=s.emp_no)t1
JOIN
(SELECT d.dept_no,max(salary) salary
FROM dept_emp d
join salaries s
on d.emp_no=s.emp_no
group by dept_no)t2
ON t1.dept_no=t2.dept_no
Where t1.salary=t2.salary
order by t1.dept_no