不合理计数结果

Question

我有两个数据表员工和部门。我想展示每个部门的员工人数，每个部门的薪水平均值等。我拥有的代码是：

select d.department_id, d.department_name, count(e.last_name) Employees,
avg(e.salary) Avg_Salary, e.last_name, e.salary, e.job_id
from departments d, employees e
where d.department_id = e.department_id
group by d.department_id, d.department_name, e.last_name, e.salary, e.job_id
order by d.department_id;

此代码运行良好，但是员工数量和平均工资的结果不是我想要的。我的结果是：

Department_ID Department_Name Employees Avg_Salary Last_Name Salary Job_ID
------------- --------------- --------- ---------- --------- ------ ------
10            Administration  1         4400       Whalen    4400   AD_ASST
20            Marketing       1         6000       Fay       6000   MK_REP
20            Marketing       1         13000      Hartstein 13000  MK_MAN

您可以看到，员工计数每个员工的显示1，而不是每个部门的总数；AVG_SALARY与薪水相同，而不是每个部门的实际工资平均值。

这是我想要的结果：

 Department_ID Department_Name Employees Avg_Salary Last_Name Salary Job_ID
-------------  --------------- --------- ---------- --------  ------ ------
10             Administration  1         4400       Whalen    4400   AD_ASST
20             Marketing       2         9500       Fay       6000   MK_REP
20             Marketing       2         9500       Smith     13000  MK_MAN

我该如何修复？

Answer 1

这应该做到 - 使用分析函数：

select d.department_id, d.department_name,
       count(e.last_name) over (partition by d.department_id) Employees,
       avg(e.salary) over (partition by d.department_id) Avg_Salary,
       e.last_name, e.salary, e.job_id
from departments d, employees e
where d.department_id = e.department_id
order by d.department_id;