SQL 工资高于部门平均水平的员工？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25229357/
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
Employees with higher salary than their department average?
提问by Solijoli
i have a table called employees which i have name, department_id and salary in it. I want to find the employees whose salary is greater than the average of their department and see their names, department_id, salary and the average salary of their department. I have written this code but it does not work. How can we fix this? Thanks in advance.
我有一个名为员工的表，里面有姓名、部门 ID 和薪水。我想找到工资高于他们部门平均工资的员工，并查看他们的姓名，department_id，工资和他们部门的平均工资。我已经写了这段代码，但它不起作用。我们怎样才能解决这个问题？提前致谢。
SELECT name, department_id, salary, avg(salary)
FROM employees
GROUP BY name, department_id, salary
HAVING salary > (select avg(salary) from employees group by department_id)
I have updated my code as you said like:
我已经更新了我的代码，如您所说：
SELECT department_id, salary, avg(salary), count(*)
FROM employees e
GROUP BY department_id, salary
HAVING salary > (select avg(salary) from employees e2 where e2.department_id=e.department_id)
But when i run this i get this result:
但是当我运行它时，我得到了这个结果：
You can see that salary and the averages are the same and there are 2 department 80's, i need 1 of all the existing departments. How can we fix this. I am using the Oracle database if that's any important. Thanks.
你可以看到工资和平均值是一样的，有 2 个部门 80 年代，我需要所有现有部门中的 1 个。我们如何解决这个问题。如果这很重要，我正在使用 Oracle 数据库。谢谢。
回答by Gordon Linoff
Your code is quite close. But, instead of a group byin the subquery, it needs to be correlatedto the outer query. And, you don't need an outer aggregation, just a whereclause:
您的代码非常接近。但是，group by它需要与外部查询相关联，而不是子查询中的 a。而且，您不需要外部聚合，只需要一个where子句：
SELECT name, department_id, salary
FROM employees e
WHERE salary > (select avg(salary) from employees e2 where e2.department_id = e.department_id);
However, you are presumably learning Oracle. You can also write this query using analytic functions:
但是，您可能正在学习 Oracle。您还可以使用分析函数编写此查询：
select e.*
from (select e.*, avg(salary) over (partition by department) as avgsalary
from employees e
) e
where e.salary > e.avgsalary;
Although this is probably a bit advanced for what you are learning, I encourage you to understand both queries.
尽管这对于您正在学习的内容来说可能有点高级，但我鼓励您理解这两个查询。
