题解 | 构建两张临时表 #获取每个部门中当前员工薪水最高的相关信息#

两张表

select deptSal.dept_no , deptSal.emp_no , deptMaxSal.maxSalary
from
(
select * , (select dept_no from dept_emp e where s.emp_no = e.emp_no) dept_no 
from salaries s
    ) deptSal , /* 部门编号,员工编号,当前薪水 */
(select dept_no,max(salary) maxSalary
from
(
select * , (select dept_no from dept_emp e where s.emp_no = e.emp_no) dept_no 
from salaries s
    ) a 
    group by dept_no
  ) deptMaxSal  /* 部门编号,当前最高薪水 */
  where
  deptSal.dept_no = deptMaxSal.dept_no
  and
  deptSal.salary = deptMaxSal.maxSalary
  order by deptSal.dept_no asc