寻觅部门薪资的平均水平

Question

我有两个表

db_employee (id,first_name,last_name,salary,department_id)

db_dept (department_id,department)

这里是一些示例数据

db_employee

id - fist_name - last_name - salary - department_id

10301 - Keith - Morgan - 27056 - 2
10302 - Tyler - Booth - 32199 - 3


db_dept

id - department

2 - 人力资源
3 - 运营

我想要输出一个表格，显示员工、他们的薪水以及该员工所在部门的平均薪水。

我尝试先使用子查询找到部门的平均薪水，然后再进行外部查询，但是我遇到了一个错误。

Select
    first_name,
    last_name,
    salary,    
    (
        select     
            avg(emp.salary),
            dep.department    
        from db_employee emp    
        join db_dept dep on emp.department_id=dep.id    
        group by dep.department
    ) As avgsaldepartment    
from db_employee

Answer 1

雷雷