MySQL 用另一列的平均值创建新列

Question

我正在尝试解决一个练习，要求我创建一个新列，其中包含每个部门员工的平均工资。我的数据库包含员工和部门表。 employees 是员工列表，其中包含每个人的工资及其department_id等数据。 Departments 包含 department_id 、 department_name 等。我已经使用 ALTER TABLE 将名为 AVG_SALARY 的列添加到 <部门。现在，我想根据 employee 表中的 salary 列填写平均工资。到目前为止，我想出了很多解决方案，但都没有奏效。其中一些是:

UPDATE departments D 
INNER JOIN employees E ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
SET AVG_SALARY = AVG(employees.SALARY);
GROUP BY DEPARTMENT_ID;

结果:错误代码:1111。组功能使用无效

UPDATE departments D
SET D.AVG_SALARY = 
(SELECT AVG(e.SALARY)
FROM employees E INNER JOIN (SELECT * FROM departments) AS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
GROUP BY D.DEPARTMENT_ID); 

RESULT: Error Code: 1242. Subquery returns more than 1 row

INSERT INTO departments (DEPARTMENT_ID, AVG_SALARY)
SELECT D.DEPARTMENT_ID, AVG(salary)
FROM EMPLOYEES E INNER JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
GROUP BY DEPARTMENT_ID;

RESULT:Error Code: 1364. Field 'DEPARTMENT_NAME' doesn't have a default value

有人可以帮忙吗？

Answer 1

尝试使用这个对您第二名所做的修改的版本:

UPDATE departments D
SET D.AVG_SALARY = 
    (SELECT AVG(e.SALARY)
     FROM employees E 
     where  D.DEPARTMENT_ID=E.DEPARTMENT_ID
     GROUP BY E.DEPARTMENT_ID);