PostgreSQL 更新表，其中包含来自另一个表的平均 vale

Question

我已经尝试了三种不同的方法，PostgreSQL仍然不喜欢我"UPDATE中不允许聚合函数"。如果不这样做，我不确定如何做到这一点。有人可以指出我正确的方向，用我的平均工资填充我的平均工资字段吗？

尝试 1：

UPDATE public."JobCategory"
    SET "AverageWage" = round(avg("Wage"), 4)
FROM public."Employees" WERE "Wage" > 0

尝试 2：

UPDATE public."JobCategory" c
INNER JOIN (
  SELECT round(avg("Wage"), 4) as average
  FROM public."Employees"
) x ON c."Index" = x."JobIndex"
SET c."AverageWage" = x.average

尝试 3：

UPDATE public."JobCategory" AS v 
SET "AverageWage" = s.round(avg("Wage"), 4)
FROM public."Employees" AS s
WHERE v."Index" = s."JobIndex"

Answer 1

您可以使用子查询执行此操作：

WITH subq AS (
    SELECT round(avg(Wage), 4) as average
    FROM public.Employees
)
UPDATE public.JobCategory jc
SET AverageWage = subq.average
FROM subq