如何用DENSE_RANK查询平均薪资第二高的部门并显示部门名称

测试表结构 #

CREATE TABLE departments(department_id, department_name) AS
SELECT 1, 'IT' FROM DUAL UNION ALL
SELECT 2, 'Sales' FROM DUAL UNION ALL
SELECT 3, 'Marketing' FROM DUAL UNION ALL
SELECT 4, 'Finance' FROM DUAL;

CREATE TABLE employees (employee_id, first_name, last_name, hire_date, salary, department_id) AS
SELECT 1, 'Lisa', 'Saladino', DATE '2001-04-03', 160000, 1 FROM DUAL UNION ALL
SELECT 2, 'Sandy', 'Herring', DATE '2011-08-04', 150200, 1 FROM DUAL UNION ALL
SELECT 3, 'Ben', 'Cooper', DATE '2019-03-05', 60700, 1 FROM DUAL UNION ALL
SELECT 4, 'Carol', 'Orr', DATE '2007-11-11', 70125,1 FROM DUAL UNION ALL
SELECT 5, 'Vicky', 'Palazzo', DATE '2004-09-17', 68525,2 FROM DUAL UNION ALL
SELECT 6, 'Cheryl', 'Ford', DATE '2020-05-10', 110000,1 FROM DUAL UNION ALL
SELECT 7, 'Leslee', 'Altman', DATE '2008-12-10', 110000, 1 FROM DUAL UNION ALL
SELECT 8, 'Jill', 'Coralnick', DATE '2001-04-11', 190000, 2 FROM DUAL UNION ALL
SELECT 9, 'Faith', 'Aaron', DATE '2001-04-17', 122000,2 FROM DUAL UNION ALL
SELECT 10, 'Debra', 'Dante', DATE '2022-10-16', 102150,4 FROM DUAL UNION ALL
SELECT 11, 'Jerry', 'Torchiano', DATE '2022-10-30', 112660,4 FROM DUAL;

完整解决方案SQL #

WITH dept_avg_sal AS (
    SELECT 
        d.department_id,
        d.department_name,
        FLOOR(AVG(e.salary)) AS department_avg,
        DENSE_RANK() OVER (ORDER BY AVG(e.salary) DESC) AS salary_rank
    FROM employees e
    JOIN departments d ON e.department_id = d.department_id
    GROUP BY d.department_id, d.department_name
)
SELECT department_id, department_name, department_avg
FROM dept_avg_sal
WHERE salary_rank = 2;

语句说明 #

1. CTE dept_avg_sal：关联employees和departments表，计算每个部门的平均薪资并取整，同时用DENSE_RANK()按平均薪资降序排名。DENSE_RANK的优势是相同薪资会获得相同排名，后续排名不会跳过（比如两个部门并列第一时，第二高部门的排名仍为2）。
2. 主查询：从CTE中筛选排名为2的记录，直接得到平均薪资第二高的部门ID、名称及平均薪资。

执行结果 #

基于提供的测试数据，执行后会返回：

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