sql - Oracle 显示高于其部门平均工资的所有员工

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

我有一个几乎可以工作的查询，但它一直给我“ORA-00904:“AVG_SAL”:无效标识符”错误。我这样做是否正确。为什么我收到此无效标识符错误？

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)。

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

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

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

更新: 尚未对此进行测试，但请尝试以下操作:

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

关于sql - Oracle 显示高于其部门平均工资的所有员工，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/3992412/