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

思路

1. 获取部门编号、每个部门员工的最高薪水

   select de.dept_no, max(s.salary) maxSalary
   from salaries s, dept_emp de
   where s.emp_no = de.emp_no
   group by de.dept_no;

2. 获取部门编号、员工编号、当前薪水

   select de.dept_no, s.emp_no, s.salary
   from salaries s, dept_emp de
   where s.emp_no = de.emp_no;

3. 连接1和2的表格，获取每个部门中当前员工薪水最高的相关信息

答案

select emp_info.dept_no, emp_info.emp_no, emp_max.maxSalary
from (
    select de.dept_no, s.emp_no, s.salary
    from salaries s, dept_emp de
    where s.emp_no = de.emp_no
    ) emp_info, /*部门编号、员工编号、当前薪水*/
    (
    select de.dept_no, max(s.salary) maxSalary
    from salaries s, dept_emp de
    where s.emp_no = de.emp_no
    group by de.dept_no
    ) emp_max /*部门编号、每个部门员工的最高薪水*/
where emp_info.dept_no = emp_max.dept_no
and emp_info.salary = emp_max.maxSalary
order by emp_max.dept_no;

优化

看过评论之后，发现没有通过 to_date 筛选当前员工，评论中更好的答案如下：

select uni.dept_no, uni.emp_no, max_salary.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
     and d.to_date = '9999-01-01'
     and s.to_date = '9999-01-01'  
    ) as uni, /* 部门编号,员工编号,当前薪水 */
    (select d.dept_no, max(s.salary) as salary
     from dept_emp d join salaries s
     on d.emp_no = s.emp_no
     and d.to_date = '9999-01-01'
     and s.to_date = '9999-01-01'  
     group by d.dept_no
    ) as max_salary /* 部门编号,当前最高薪水 */
where uni.salary = max_salary.salary
and uni.dept_no = max_salary.dept_no
order by uni.dept_no;