PostgreSQL用其他表平均值更新字段报错，求正确实现方案

我明白你遇到的这个头疼问题了——PostgreSQL确实不让在UPDATE的SET子句里直接用聚合函数（比如avg()），毕竟聚合函数是针对一组数据计算的，而UPDATE是逐行处理的，直接放进去数据库根本搞不清你要基于哪组数据算平均值。

先拆解下你之前尝试的问题：

• 尝试1：既没有按职位类别分组计算平均工资，也没建立JobCategory和Employees的关联关系（比如缺少类似c."CategoryId" = e."JobCategoryId"的匹配条件），数据库完全不知道每个职位类别对应哪些员工的工资。
• 尝试2（虽然没写完）：即使用INNER JOIN的方式，也得先预计算好每个类别的平均工资，再关联更新，不能直接把聚合函数丢进SET里。

正确的解决方案 #

你需要先通过子查询算出每个JobCategory对应的平均工资，再把这个结果集和JobCategory表关联起来做更新，具体写法如下（注意替换实际的关联字段，这里假设用Id作为JobCategory的主键，Employees用JobCategoryId关联）：

UPDATE public."JobCategory" c
SET "AverageWage" = sub.average_wage
FROM (
    SELECT 
        "JobCategoryId",
        round(avg("Wage"), 4) AS average_wage
    FROM public."Employees"
    WHERE "Wage" > 0
    GROUP BY "JobCategoryId"  -- 按职位类别分组，算出每个类别的平均工资
) sub
WHERE c."Id" = sub."JobCategoryId";  -- 关联两张表的匹配字段

关键点说明 #

• 子查询sub先完成聚合计算：按职位类别分组后，每个类别只会得到一行平均工资数据，这样数据库就能明确知道每个职位类别对应的值。
• 再通过关联字段把JobCategory表和子查询结果匹配，把预计算好的平均工资赋值给AverageWage字段。

如果有些职位类别没有对应的员工数据（子查询里没该类别的记录），你可以用LEFT JOIN+COALESCE处理，给这些类别设置默认值（比如0或者NULL）：

UPDATE public."JobCategory" c
SET "AverageWage" = COALESCE(sub.average_wage, 0)  -- 无数据时设为0，也可换成NULL
FROM (
    SELECT 
        "JobCategoryId",
        round(avg("Wage"), 4) AS average_wage
    FROM public."Employees"
    WHERE "Wage" > 0
    GROUP BY "JobCategoryId"
) sub
WHERE c."Id" = sub."JobCategoryId"
OR sub."JobCategoryId" IS NULL;

这样就能绕过PostgreSQL的限制，正确填充每个职位类别的平均工资啦。

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