mysql - 更新 MYSQL 平均分

Question

我有两个表 Fluids 和 CAS，我正在尝试使用 CAD 表中的平均分数更新相关表 Fluids。像这样的事情:

UPDATE fluids INNER JOIN cas on cas.cas = fluids.cas 
SET fluids.score = avg(cas.score) GROUP BY cas.cas

Answer 1

您需要将聚合选择包装为子查询:

UPDATE fluids 
INNER JOIN (
    SELECT cas,avg(cas.score) as avg_score
    FROM cas
    GROUP BY cas 
) as c ON c.cas = fluids.cas 
SET fluids.score = c.avg_score