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

Question

我正在写一个查询，以查找收入高于其部门平均工资的员工。我需要显示员工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

Answer 1

我不相信您可以在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