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

经过了各种数据集的情况，我这个应该是都没问题了吧

SELECT ms.dept_no, s2.emp_no, ms.maxSalary
FROM salaries s2, dept_emp d2,
(SELECT d1.dept_no, MAX(s1.salary) AS maxSalary
FROM dept_emp d1 JOIN salaries s1 ON d1.emp_no = s1.emp_no
GROUP BY d1.dept_no) ms
WHERE s2.salary = ms.maxSalary AND s2.emp_no = d2.emp_no AND d2.dept_no = ms.dept_no
ORDER BY ms.dept_no

后来发现了第二种方法：
就是先把两张表联结起来，用开窗函数对每个部门里的人的工资排名，取每部门的排名为1的人出来就好了
SELECT dept_no, emp_no, maxSalary FROM
(SELECT dept_no, emp_no, salary AS maxSalary, RANK() OVER(PARTITION BY dept_no ORDER BY salary DESC) AS ranking FROM
(SELECT d.emp_no, d.dept_no, s.salary FROM dept_emp d JOIN salaries s ON d.emp_no = s.emp_no) uni) total
WHERE ranking = 1