甲骨文向所有员工展示其部门薪水高于平均水平的员工

问题描述

我正在写一个查询，以查找收入高于其部门平均工资的员工.我需要显示员工ID，薪水，部门ID和该部门的平均工资.

I am writing a query to find employees who earn greater than the average salary within their department. I need to display the employee ID, salary, department id, and average salary of that department.

我有一个几乎可以正常使用的查询，但是它一直给我"ORA-00904:"AVG_SAL":无效的标识符错误.我正确地做到了吗?为什么我会收到此无效的标识符错误?

I have a query that just almost works but it keeps giving me "ORA-00904: "AVG_SAL": invalid identifier" errors. Am I doing this correctly. Why am i getting this invalid identifier error?

SELECT employee_id, salary, department_id,
  (SELECT ROUND(AVG(salary),2)
  FROM employees e_inner
  WHERE e_inner.department_id = e.department_id) AS avg_sal
FROM employees e
WHERE salary > avg_sal
ORDER BY avg_sal DESC

推荐答案

我不相信您可以在WHERE子句中引用列别名(在这种情况下为avg_sal).

I don't believe you can refer to a column alias (avg_sal in this case) in a WHERE clause.

您需要重复该内部查询，即:

You'll need to repeat that inner query, i.e.:

SELECT employee_id, salary, department_id,
  (SELECT ROUND(AVG(salary),2)
  FROM employees e_inner
  WHERE e_inner.department_id = e.department_id) AS avg_sal
FROM employees e
WHERE salary > 
 (SELECT ROUND(AVG(salary),2)
  FROM employees e_inner
  WHERE e_inner.department_id = e.department_id)
ORDER BY avg_sal DESC

这两个内部查询不是很好，但这是纠正错误的最直接的方法.

Not great, with those two inner queries, but that's the most-straightforward way to correct the error.

更新:尚未测试，请尝试以下操作:

Update: Haven't tested this, but try the following:

SELECT e.employee_id, e.salary, e.department_id, b.avg_sal
FROM employees e
INNER JOIN
(SELECT department_id, ROUND(AVG(salary),2) AS avg_sal
 FROM employees
 GROUP BY department_id) e_avg ON e.department_id = e_avg.department_id AND e.salary > e_avg.avg_sal
ORDER BY e_avg.avg_sal DESC

这篇关于甲骨文向所有员工展示其部门薪水高于平均水平的员工的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！