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

select
  t1.dept_no,
  t1.emp_no,
  t2.maxSalary
from
  (
    select
      de.dept_no,
      de.emp_no,
      s.salary
    from
      dept_emp de,
      salaries s
    where
      de.emp_no = s.emp_no
  ) as t1,
  (
    select
      de.dept_no as dept_no,
      max(salary) as maxSalary
    from
      dept_emp as de,
      salaries as s
    where
      de.emp_no = s.emp_no
    group by
      dept_no
  ) as t2
where
  t1.salary = t2.maxSalary
  and t1.dept_no = t2.dept_no
order by
  dept_no