查找部门平均工资

Question

我有两个表

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

Here are same sample Data
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 - human resources
3 - operation

我试图输出一个表格，显示员工、他们的工资以及该员工所在部门的平均工资。

我试着做一个子查询，先找到部门的平均工资。然后做一个外部查询，但我得到了一个错误

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

Answer 2

SELECT 
emp.first_name,
emp.last_name,
emp.salary,
AVG(emp.salary) OVER ( PARTITION BY dep.id) as avgsalarydep
FROM db_employee emp
INNER JOIN db_dept dep ON department_id=id