SELECT a.dept_no dept_no, b.emp_no emp_no a.salary Maxsarary, FROM ( SELECT max( salary ) salary, d.dept_no FROM salaries s LEFT JOIN dept_emp d ON s.emp_no = d.emp_no GROUP BY d.dept_no ) a LEFT JOIN ( SELECT s.salary, d.dept_no, d.emp_no FROM salaries s LEFT JOIN dept_emp d ON s.emp_no = d.emp_no ) b ON a.salary = b.salary AND a.dept_no = b.dept_no ORDER BY a.salary
1: 派生表 a
查询每个部门中工资最高的数据 部门id和工资信息
( SELECT max( salary ) salary, d.dept_no FROM salaries s LEFT JOIN dept_emp d ON s.emp_no = d.emp_no GROUP BY d.dept_no ) a
2: 派生表 b
查询所有的信息 员工id, 部门id, 薪水
( SELECT d.emp_no, d.dept_no, s.salary FROM salaries s LEFT JOIN dept_emp d ON s.emp_no = d.emp_no ) b
3: 聚合表a和表b
根据部门id和最高薪水聚合表a,b, 根据部门id排序
SELECT a.dept_no dept_no, b.emp_no emp_no a.salary Maxsarary, FROM a LEFT JOIN b ON a.salary = b.salary AND a.dept_no = b.dept_no ORDER BY a.salary