尋找部門薪資的平均水平

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

SELECT 
emp.first_name,
emp.last_name,
salary,
demp.avg_salary
FROM db_employee emp
  INNER JOIN db_dept dep ON emp.department_id=dep.id
  INNER JOIN (
     SELECT
     AVG(salary) avg_salary,
     department
     FROM db_employee
       INNER JOIN db_dept ON department_id=id ) demp 
           ON demp.department=dep.department