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

通过两张表解决，一张表存放所有员工的部门和薪水，一张表根据部门分组存放部门里工资最高的记录

select uni.dept_no,uni.emp_no,maxSalary.salary 
from
(select dept_emp.dept_no,salaries.emp_no,salaries.salary
from salaries,dept_emp 
where salaries.emp_no=dept_emp.emp_no
and salaries.to_date='9999-01-01' and
dept_emp.to_date='9999-01-01') as uni,
(select dept_emp.dept_no,max(salaries.salary) as salary
from salaries,dept_emp 
where salaries.emp_no=dept_emp.emp_no
and salaries.to_date='9999-01-01' and
dept_emp.to_date='9999-01-01'
 group by dept_emp.dept_no
) as maxSalary
where uni.salary=maxSalary.salary
and uni.dept_no=maxSalary.dept_no
order by uni.dept_no