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

获取所有部门中当前(dept_emp.to_date = '9999-01-01')员工当前(salaries.to_date='9999-01-01')薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary，按照部门升序排列。
法一：开窗函数

select t.dept_no,t.emp_no,t.salary from
(
select 
    de.dept_no,de.emp_no,s.salary,
    row_number() over(partition by de.dept_no order by s.salary desc) as rk
from (
    select * from dept_emp where to_date='9999-01-01') de
    inner join
    (select * from salaries where to_date='9999-01-01') s on de.emp_no=s.emp_no
)t
where t.rk=1;

法二：关联子查询，外表固定一个部门，内表进行子查询

SELECT d1.dept_no, d1.emp_no, s1.salary
FROM dept_emp as d1
INNER JOIN salaries as s1
ON d1.emp_no=s1.emp_no
AND d1.to_date='9999-01-01'
AND s1.to_date='9999-01-01'
WHERE s1.salary in (SELECT MAX(s2.salary)
FROM dept_emp as d2
INNER JOIN salaries as s2
ON d2.emp_no=s2.emp_no
AND d2.to_date='9999-01-01'
AND s2.to_date='9999-01-01'
AND d2.dept_no = d1.dept_no
)
ORDER BY d1.dept_no;

补充：如果这题不需要给出emp_no(即只求所有部门中当前员工薪水最高值)，则用INNER JOIN和GROUP BY和MAX即可解决：

SELECT d.dept_no, MAX(s.salary)
FROM dept_emp as d
INNER JOIN salaries as s
ON d.emp_no=s.emp_no
AND d.to_date='9999-01-01'
AND s.to_date='9999-01-01'
GROUP BY d.dept_no;