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

别人代码： SELECT currentsalary.dept_no, currentsalary.emp_no, currentsalary.salary AS salary FROM -- 创建maxsalary表用于存放当前每个部门薪水的最大值 (SELECT d.dept_no, MAX(s.salary) AS salary FROM salaries AS s INNER JOIN dept_emp As d ON d.emp_no = s.emp_no WHERE d.to_date = '9999-01-01' AND s.to_date = '9999-01-01' GROUP BY d.dept_no) AS maxsalary, -- 创建currentsalary表用于存放当前每个部门所有员工的编号和薪水 (SELECT d.dept_no, s.emp_no, s.salary FROM salaries AS s INNER JOIN dept_emp As d ON d.emp_no = s.emp_no WHERE d.to_date = '9999-01-01' AND s.to_date = '9999-01-01' ) AS currentsalary -- 限定条件为两表的dept_no和salary均相等 WHERE currentsalary.dept_no = maxsalary.dept_no AND currentsalary.salary = maxsalary.salary -- 最后以currentsalary.dept_no排序输出符合要求的记录表 ORDER BY currentsalary.dept_no

自己代码： select a.dept_no,b.emp_no,a.salary from (select b.dept_no, max(a.salary) as salary from salaries a inner join dept_emp b on a.emp_no = b.emp_no group by b.dept_no) a left join salaries b on a.salary =b.salary;