我有两个表
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 - 运营
我想要输出一个表格，显示员工、他们的薪水以及该员工所在部门的平均薪水。
我尝试先使用子查询找到部门的平均薪水，然后再进行外部查询，但是我遇到了一个错误。
1
1128Select
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 
Copyright 2014-2025 https://www.php.cn/ All Rights Reserved | php.cn | 湘ICP备2023035733号
收藏
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