所有部门中当前员工当前薪水最高的相关信息

刚开始的思路：联结两个表后使用GROUP BY 和MAX聚合函数，但是在mysql环境中通不过

SELECT dept_no,dept_emp.emp_no,MAX(salary) AS salary
FROM dept_emp  INNER JOIN salaries
ON dept_emp.emp_no=salaries.emp_no
WHERE dept_emp.to_date = '9999-01-01'
AND salaries.to_date='9999-01-01'
GROUP BY dept_no
ORDER BY dept_no

后来看了评论区的分析，重新整理：
先找出各个部门最高的薪资，再找到各个员工的部门、薪资，联结查询后的两个表，这里筛选时需要两个条件，一个是部门，一个是最高薪资

SELECT a.dept_no,b.emp_no,a.salary
FROM 
    (SELECT dept_no,MAX(salary) AS salary
    FROM salaries
    INNER JOIN dept_emp
    ON dept_emp.emp_no=salaries.emp_no
    WHERE dept_emp.to_date = '9999-01-01'
    AND salaries.to_date='9999-01-01'
    GROUP BY dept_no) AS a
INNER JOIN 
    (SELECT dept_no,dept_emp.emp_no,salary
    FROM dept_emp
    INNER JOIN salaries
    ON dept_emp.emp_no=salaries.emp_no
    WHERE dept_emp.to_date = '9999-01-01'
    AND salaries.to_date='9999-01-01')AS b
ON a.salary=b.salary
AND a.dept_no=b.dept_no
ORDER BY dept_no