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 后面!)