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

描述

有一个员工表dept_emp简况如下:

有一个薪水表salaries简况如下:

获取每个部门中当前员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary，按照部门编号升序排列，以上例子输出如下:

 数据：

INSERT INTO dept_emp VALUES(10001,'d001','1986-06-26','9999-01-01');
INSERT INTO dept_emp VALUES(10002,'d001','1996-08-03','9999-01-01');
INSERT INTO dept_emp VALUES(10003,'d004','1995-12-03','9999-01-01');
INSERT INTO dept_emp VALUES(10004,'d004','1986-12-01','9999-01-01');
INSERT INTO dept_emp VALUES(10005,'d003','1989-09-12','9999-01-01');
INSERT INTO dept_emp VALUES(10006,'d002','1990-08-05','9999-01-01');
INSERT INTO dept_emp VALUES(10007,'d005','1989-02-10','9999-01-01');
INSERT INTO dept_emp VALUES(10009,'d006','1985-02-18','9999-01-01');
INSERT INTO dept_emp VALUES(10010,'d006','2000-06-26','9999-01-01');

INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10003,43311,'2001-12-01','9999-01-01');
INSERT INTO salaries VALUES(10004,74057,'2001-11-27','9999-01-01');
INSERT INTO salaries VALUES(10005,94692,'2001-09-09','9999-01-01');
INSERT INTO salaries VALUES(10006,43311,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10007,88070,'2002-02-07','9999-01-01');
INSERT INTO salaries VALUES(10009,95409,'2002-02-14','9999-01-01');
INSERT INTO salaries VALUES(10010,94409,'2001-11-23','9999-01-01');

我最开始的解法是

SELECT dept_no,
        de.emp_no,
        max(salary) over(partition by dept_no order by salary) maxSalary
from dept_emp de
join salaries s
on de.emp_no=s.emp_no
group by dept_no
order by dept_no

但是运行发现同在d004部门的员工003和004出现了问题

 

 原因是groupby默认显示非聚合的第一条数据，并不能直接显示最高工资对应的员工编号

后来尝试用了开窗函数max over()，同样的，只要最后要用groupby都会有这个问题

于是尝试子查询，先把每个部门最高工资查出，生成r表，再三表联查，加上过滤条件r.maxSalary=s.salary 即可完成。同时如果一个部门有多个相同的最高工资，也可全部查出。

select r.dept_no,s.emp_no,r.maxSalary from (
select d.dept_no,max(s.salary) maxSalary from dept_emp d,salaries s
where d.emp_no=s.emp_no
group by d.dept_no
)r join dept_emp d
on r.dept_no=d.dept_no
join salaries s
on d.emp_no=s.emp_no
and r.maxSalary=s.salary
order by r.dept_no

 其实这道题还容易有一个bug：例如1部门最高工资是1000,2部门非最高工资也有个1000，那么有可能查询的时候会把2部门这个员工也显示出来

但上面的代码也可避免这个问题，目前是我想到的最优解了。如果大家有其他的解法也可讨论，共同进步