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

解题思路

1.联结表dept_emp和表salary,查询出每个部门薪水最高的员工的dept_no和salary,作为新表a

SELECT dept_emp.dept_no,MAX(salaries.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_emp.dept_no;

2.将dept_emp表和salary表联结，作为新表b

SELECT dept_emp.dept_no,dept_emp.emp_no,salaries.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';

3.联结表a和表b,获取表a中的dept_no,salary字段，表b中的emp_no字段，并按照表a的部门编号升序排列

SELECT a.dept_no,b.emp_no,a.salary AS maxSalary
FROM 

(SELECT dept_emp.dept_no,MAX(salaries.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_emp.dept_no) AS a

INNER JOIN

(SELECT dept_emp.dept_no,dept_emp.emp_no,salaries.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 a.dept_no;