oracle 平均工资第二高的部门

uinbv5nw 于 2023-08-03 发布在 Oracle

关注(0)|答案(1)|浏览(98)

我正在尝试将此查询转换为使用DENSE_RANK，这样我就可以找到平均工资第二高的department_id。另外，我想把department_name也添加到输出中，但是我似乎不能让它工作。如有任何帮助，我们将不胜感激。

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;

SELECT department_id,  floor(AVG(salary)) department_avg
 FROM employees 
 GROUP BY department_id HAVING AVG(salary) = (SELECT MAX(AVG(salary)) FROM employees 
GROUP BY department_id)

字符串

oracle

来源：https://stackoverflow.com/questions/76821313/department-with-second-highest-avg-salary

1条答案

按热度按时间

w7t8yxp51#

您可以用途：

SELECT department_id, 
       floor(AVG(salary)) department_avg
FROM   employees 
GROUP BY department_id
ORDER BY department_avg DESC
OFFSET 1 ROW FETCH NEXT 1 ROW ONLY;

字符串
或者：

SELECT department_id,
       department_avg
FROM   (
  SELECT department_id, 
         floor(AVG(salary)) AS department_avg,
         DENSE_RANK() OVER (ORDER BY AVG(salary) DESC) AS rnk
  FROM   employees 
  GROUP BY department_id
)
WHERE  rnk = 2;

型
这两个输出：
| 部门_平均| DEPARTMENT_AVG |
| --| ------------ |
| 110170| 110170 |
fiddle
或者，包括名称：

SELECT d.department_id, 
       MAX(d.department_name) AS department_name,
       COALESCE(floor(AVG(salary)), 0) AS department_avg
FROM   departments d
       LEFT OUTER JOIN employees e
       ON (e.department_id = d.department_id)
GROUP BY d.department_id
ORDER BY department_avg DESC
OFFSET 1 ROW FETCH NEXT 1 ROW ONLY;

型
或者：

SELECT department_id,
       department_name,
       department_avg
FROM   (
  SELECT d.department_id, 
         MAX(d.department_name) AS department_name,
         COALESCE(floor(AVG(salary)), 0) AS department_avg,
         DENSE_RANK() OVER (ORDER BY COALESCE(floor(AVG(salary)), 0) DESC) AS rnk
  FROM   departments d
         LEFT OUTER JOIN employees e
         ON (e.department_id = d.department_id)
  GROUP BY d.department_id
)
WHERE  rnk = 2;

型
fiddle的

赞(0）回复(0）举报 2023-08-03

我来回答

oracle 平均工资第二高的部门

1条答案

相关问题

热门标签

最新问答