使用avg连接两个表

Question

我有两个表员工:

employee_number employee_name salarydivison_id

部门:

division_iddivision_name

如何显示每个部门中所有员工的员工编号、员工姓名、division_name和平均工资。

Answer 1

您可以尝试:

SELECT employee.employee_number, employee.employee_name, division.division_name, 
AVG(salary) AS AvgSalary
FROM employee
JOIN division ON division.division_id = employee.division_id
GROUP BY employee_number, employee_name, division_name

然而，我不确定这是否会达到你想要的结果。

要显示"每个部门所有员工的平均工资"，您可以这样做:

SELECT division.division_name AS Division, AVG(salary) AS AvgSalary FROM division
JOIN employee ON employee.division_id = division.division_id
GROUP BY division_name

Answer 2

SELECT employee_number, employee_name, division_name, AVG(amount)
FROM employee
JOIN division ON division.division_id=employee.division_id
GROUP BY division_name;

Answer 3

如果你的数据库支持分析功能，那么你可以这样使用它:

SELECT e.employee_number, e.employee_name, d.division_name, 
AVG(salary) over (partition by d.division_id) as avg_sal_div
FROM employee e
JOIN division d ON d.division_id=e.division_id;