MySQL中为departments表填充各部门平均薪资列的问题求助

我来帮你搞定这个问题！先拆解下你之前几次尝试失败的原因，再给你靠谱的解决方案：

先看你之前的错误原因 #

• 第一个SQL报错（1111）
  你写的语句：

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

  问题出在：MySQL不允许直接在UPDATE的JOIN逻辑后加GROUP BY。UPDATE是针对单行记录的修改操作，而GROUP BY是把多行数据聚合为一行，两者的执行逻辑冲突，所以触发了Invalid use of group function错误。

• 第二个SQL报错（1242）
  你写的语句：

  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);
  

  问题出在：你的子查询会返回所有部门的平均薪资列表（多行结果），但外层的UPDATE没有把子查询的结果和当前要修改的departments行做关联，数据库不知道该把哪个平均值对应到哪个部门，所以报了Subquery returns more than 1 row。

• 第三个SQL报错（1364）
  你写的语句：

  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;
  

  问题出在：你用错了操作！INSERT是新增行，而你要做的是更新已存在的部门行。另外，departments表的DEPARTMENT_NAME字段没有默认值，INSERT时没给这个字段赋值，自然就触发了Field 'DEPARTMENT_NAME' doesn't have a default value错误。

正确的解决方案 #

这里给你两种常用的写法，任选其一即可：

写法一：JOIN子查询更新（推荐，性能更优）

先通过子查询算出每个部门的平均薪资，再和departments表关联更新：

UPDATE departments D
JOIN (
    SELECT department_id, AVG(salary) AS avg_sal
    FROM employees
    GROUP BY department_id
) AS E ON D.department_id = E.department_id
SET D.AVG_SALARY = E.avg_sal;

逻辑说明：子查询先计算出每个部门的平均薪资，生成一个临时表；再把临时表和departments表用department_id关联，将对应的平均值写入AVG_SALARY列。

写法二：关联子查询更新（更简洁）

针对departments的每一行，通过子查询匹配对应部门的平均薪资：

UPDATE departments D
SET D.AVG_SALARY = (
    SELECT AVG(salary)
    FROM employees E
    WHERE E.department_id = D.department_id
);

逻辑说明：对departments里的每一条记录，子查询都会根据当前行的department_id去employees表计算对应的平均薪资，然后赋值给AVG_SALARY。如果某个部门没有员工，AVG_SALARY会被设为NULL，这是合理的。

额外优化：处理无员工的部门 #

如果想把没有员工的部门的AVG_SALARY设为0（或其他默认值），可以用COALESCE函数处理：

UPDATE departments D
SET D.AVG_SALARY = COALESCE(
    (SELECT AVG(salary) FROM employees E WHERE E.department_id = D.department_id),
    0
);

内容的提问来源于stack exchange，提问作者czarzan