员工的工资高于部门平均水平? [英] Employees with higher salary than their department average?

问题描述

我有一个名为employees的表，其中有姓名，department_id和薪水.我想找到薪水高于其部门平均水平的员工，并查看其姓名，部门ID，薪水和部门平均工资.我已经编写了此代码，但是它不起作用.我们该如何解决呢?预先感谢.

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.

    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:

您可以看到薪水和平均工资相同，并且有2个部门80年代，我需要所有现有部门中的1个.我们如何解决这个问题.如果这很重要，我正在使用Oracle数据库.谢谢.

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.

推荐答案

您的代码非常接近.但是，除了子查询中的group by之外，它还需要与外部查询进行关联.而且，您不需要外部聚合，只需一个where子句:

Your code is quite close. But, instead of a group by in the subquery, it needs to be correlated to the outer query. And, you don't need an outer aggregation, just a where clause:

SELECT name, department_id, salary
FROM employees e
WHERE salary > (select avg(salary) from employees e2 where e2.department_id = e.department_id);

但是，您大概正在学习Oracle.您也可以使用解析函数编写此查询:

However, you are presumably learning Oracle. You can also write this query using analytic functions:

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.

这篇关于员工的工资高于部门平均水平?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！