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

select max_salary.dept_no,emp_temp.emp_no,maxSalary from (SELECT dept_no,MAX(salary) as maxSalary
                                               FROM (SELECT dept_no,salaries.emp_no,salary
                                                     FROM dept_emp,salaries WHERE dept_emp.emp_no=salaries.emp_no AND
                                                                                                         dept_emp.to_date='9999-01-01' AND salaries.to_date='9999-01-01') temp
                                               GROUP BY dept_no) max_salary,(SELECT dept_no,salaries.emp_no,salary
                                                                             FROM dept_emp,salaries 
                                                                                                                                                         WHERE dept_emp.emp_no=salaries.emp_no) emp_temp
where maxSalary=salary and max_salary.dept_no=emp_temp.dept_no
ORDER BY dept_no