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

1. 题目

• 题目来源：SQL12 获取每个部门中当前员工薪水最高的相关信息

• 题目描述
  获取每个部门中当前员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary，按照部门编号dept_no升序排列

• 表格
  

• 所需结果
  

2. 题解

思路：在子查询中使用窗口函数，排序窗口函数和最大函数窗口函数都可以。

1、排序窗口函数

# 排序窗口函数
SELECT t.dept_no, t.emp_no, t.salary maxSalary
FROM (SELECT de.dept_no, de.emp_no, s.salary,
             RANK() OVER(PARTITION BY de.dept_no ORDER BY s.salary DESC) rk
      FROM salaries s
      JOIN dept_emp de 
      ON de.emp_no = s.emp_no
         AND de.to_date = '9999-01-01' # 因为是INNER JOIN 也可以放到WHERE中
         AND s.to_date = '9999-01-01'
  ) t
WHERE t.rk = 1 
ORDER BY t.dept_no ASC

2、最大函数窗口函数

# 最大窗口函数
SELECT t.dept_no, t.emp_no, t.salary maxSalary
FROM (SELECT de.dept_no, de.emp_no, s.salary,
             MAX(salary) OVER(PARTITION BY de.dept_no ORDER BY s.salary DESC) max_s
      FROM salaries s
      JOIN dept_emp de 
      ON de.emp_no = s.emp_no
         AND de.to_date = '9999-01-01'
         AND s.to_date = '9999-01-01'
  ) t
WHERE salary = t.max_s 
ORDER BY t.dept_no ASC

注意：隐含条件：当前员工，以及对工资倒序排列

反思：窗口函数

这提示典型的窗口函数：在外表的WHERE条件中使用排序，所以需要将窗口函数放在子查询中。
（SQL中SELECT 执行在 WHERE 后面！）