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

2021-12-24 00:00:00 sql oracle plsql ora-00904

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

相关文章