问题描述
我正在编写查询以查找收入高于其部门平均工资的员工.我需要显示该部门的员工 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
这篇关于Oracle 显示高于其部门平均工资的所有员工的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!