如何按Pcode计算Cost-price平均值并更新至tbProduct的Avg_costprice列

核心思路是先从tbStockIn按Pcode分组计算对应成本价的平均值，再通过关联tbProduct表将结果更新到Avg_costprice列。以下是主流数据库的具体实现代码：

MySQL 版本 #

UPDATE tbProduct p
JOIN (
    -- 先分组计算每个Pcode的平均成本
    SELECT Pcode, AVG(`Cost-price`) AS avg_cost
    FROM tbStockIn
    GROUP BY Pcode
) s ON p.Pcode = s.Pcode
SET p.Avg_costprice = s.avg_cost;

SQL Server 版本 #

UPDATE p
SET p.Avg_costprice = s.avg_cost
FROM tbProduct p
INNER JOIN (
    SELECT Pcode, AVG([Cost-price]) AS avg_cost
    FROM tbStockIn
    GROUP BY Pcode
) s ON p.Pcode = s.Pcode;

PostgreSQL 版本 #

UPDATE tbProduct p
SET Avg_costprice = s.avg_cost
FROM (
    SELECT Pcode, AVG("Cost-price") AS avg_cost
    FROM tbStockIn
    GROUP BY Pcode
) s
WHERE p.Pcode = s.Pcode;

注意事项 #

• 由于列名Cost-price包含连字符，不同数据库需要用对应符号包裹：MySQL用反引号` `，SQL Server用方括号[]，PostgreSQL用双引号""，否则会触发语法错误。
• 如果tbStockIn中存在tbProduct没有的Pcode，这些记录不会影响tbProduct；反过来，如果tbProduct的某个Pcode在tbStockIn中无匹配记录，其Avg_costprice会保留原有值，若要统一设为NULL，可以改用LEFT JOIN并配合COALESCE函数处理。
• 执行更新前建议单独运行子查询（即括号内的SELECT语句），确认计算出的平均值符合预期，避免误更新数据。

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