SQL Oracle 显示高于其部门平均工资的所有员工
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3992412/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle show all employees with greater than average salary of their department
提问by ChrisOPeterson
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.
我正在编写一个查询，以查找收入高于其部门平均工资的员工。我需要显示该部门的员工 ID、工资、部门 ID 和平均工资。
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?
我有一个几乎可以工作的查询，但它一直给我“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
采纳答案by Dan J
I don't believe you can refer to a column alias (avg_sal in this case) in a WHERE clause.
我不相信您可以在 WHERE 子句中引用列别名（在本例中为 avg_sal）。
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
回答by RussellH
More efficient to use analytics:
更有效地使用分析：
select employee_id, salary, department_id, avg_sal
from
(
SELECT employee_id, salary, department_id,
round(avg(salary) over (partition by department_id), 2) avg_sal
from emp
)
where salary > avg_sal
order by avg_sal desc
回答by Andomar
You could rewrite it as a join:
您可以将其重写为连接：
SELECT e1.employee_id
, e1.salary
, e1.department_id
, ROUND(AVG(e2.salary),2) as Avg_Sal
FROM employees e
JOIN employees e2
ON e2.department_id = e.department_id
GROUP BY
e1.employee_id
, e1.salary
, e1.department_id
HAVING e1.salary > ROUND(AVG(e2.salary),2)
Or a subquery:
或子查询：
SELECT *
FROM (
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
) as SubqueryAlias
WHERE salary > avg_sal
回答by Amr Mahmoud Ezzat
select *
from employees e
join(
select Round(avg(salary)) AvgSal,department_id,department_name as dept_name
from employees join departments
using (department_id)
group by department_id,department_name
) dd
using(department_id)
where e.salary > dd.AvgSal;
another solution
另一种解决方案
select *
from employees e,
(
select
department_id,
avg(salary) avg_sal
from employees
group by department_id
) e1
where e.department_id=e1.department_id
and e.salary > e1.avg_sal
