Oracle用RANK/DENSE_RANK重写查询取各部门最高薪员工

重写后的SQL如下，输出结果和原有查询完全一致，同时满足提出的所有要求：

WITH emp_with_rank AS (
    SELECT
        employee_id,
        first_name,
        last_name,
        department_id,
        sal,
        RANK() OVER (PARTITION BY department_id ORDER BY sal DESC) AS rk
    FROM employees
)
SELECT
    e.employee_id,
    e.first_name,
    e.last_name,
    e.department_id,
    d.department_name,
    e.sal
FROM emp_with_rank e
INNER JOIN dept d
    ON e.department_id = d.department_id
WHERE e.rk = 1;

逻辑说明 #

• 选择RANK()函数实现排名：按部门分区、薪资降序排名时，同部门相同薪资的员工会拿到相同排名，最高薪的排名固定为1，自然会保留department_id=1部门中两名同薪最高的员工记录，和原查询NOT EXISTS逻辑的返回结果完全匹配。
• 关联逻辑优化：先在CTE（公用表表达式）中完成员工表的排名计算，之后仅执行一次员工表和部门表的department_id关联匹配，无重复关联判断。
• 若替换为DENSE_RANK()函数，取排名为1的结果和RANK()完全一致，两者的排名差异仅出现在非最高薪的后续排名段，不影响本次最高薪筛选的结果。

执行结果 #

和原查询返回完全一致：

附：测试用表及原查询参考 #

-- 测试建表语句
CREATE table dept  (department_id, department_name) AS
SELECT 1, 'IT' FROM DUAL UNION ALL
SELECT 2, 'SALES'  FROM DUAL;

CREATE TABLE employees (employee_id, manager_id, first_name, last_name, department_id, sal,
serial_number) AS
SELECT 1, NULL, 'Alice', 'Abbot', 1, 100000, 'D123' FROM DUAL UNION ALL
SELECT 2, 1, 'Beryl', 'Baron',1, 50000,'D124' FROM DUAL UNION ALL
SELECT 3, 1, 'Carol', 'Chang',1, 100000, 'A1424' FROM DUAL UNION ALL
SELECT 4, 2, 'Debra', 'Dunbar',1, 75000, 'A1425' FROM DUAL UNION ALL
SELECT 5, NULL, 'Emily', 'Eden',2, 90000, 'C1725' FROM DUAL UNION ALL
SELECT 6, 3, 'Fiona', 'Finn',1, 88500,'C1726' FROM DUAL UNION ALL
SELECT 7,5, 'Grace', 'Gelfenbein',2, 55000, 'C1727' FROM DUAL;

-- 原有可正常运行的查询
select 
  e.employee_id,
  e.first_name,
  e.last_name,
  e.department_id,
  d.department_name,
  e.sal
from employees e join dept d on e.department_id = d.department_id
  where not exists
    ( select null
      from   employees 
      where  department_id = e.department_id
      and    sal > e.sal );

内容的提问来源于stack exchange，提问作者Pugzly