如何通过多表关联一步完成无直接关联表的求和更新操作？

你可以通过子查询聚合+关联更新的方式，在单条语句里完成需求，核心思路是先把X2和X3关联后按X2.A分组求和，再将聚合结果与X1关联完成更新。以下是主流数据库的实现示例：

MySQL 写法 #

UPDATE X1
JOIN (
    -- 先计算每个X2.A对应的X3人口总和
    SELECT X2.A, SUM(X3.POPULATION) AS total_pop
    FROM X2
    JOIN X3 ON X2.B = X3.B
    GROUP BY X2.A
) AS agg ON X1.A = agg.A
SET X1.POPULATION = agg.total_pop;

如果需要处理X1中存在无对应A的记录（希望将这类记录的POPULATION设为0），可以改用LEFT JOIN+COALESCE：

UPDATE X1
LEFT JOIN (
    SELECT X2.A, SUM(X3.POPULATION) AS total_pop
    FROM X2
    JOIN X3 ON X2.B = X3.B
    GROUP BY X2.A
) AS agg ON X1.A = agg.A
SET X1.POPULATION = COALESCE(agg.total_pop, 0);

PostgreSQL 写法 #

PostgreSQL使用FROM子句关联聚合结果：

UPDATE X1
SET POPULATION = agg.total_pop
FROM (
    SELECT X2.A, SUM(X3.POPULATION) AS total_pop
    FROM X2
    JOIN X3 ON X2.B = X3.B
    GROUP BY X2.A
) AS agg
WHERE X1.A = agg.A;

处理无对应记录的情况：

UPDATE X1
SET POPULATION = COALESCE(agg.total_pop, 0)
FROM (
    SELECT X2.A, SUM(X3.POPULATION) AS total_pop
    FROM X2
    JOIN X3 ON X2.B = X3.B
    GROUP BY X2.A
) AS agg
WHERE X1.A = agg.A;

-- 额外更新无匹配的记录
UPDATE X1
SET POPULATION = 0
WHERE NOT EXISTS (
    SELECT 1 FROM X2 WHERE X2.A = X1.A
);

SQL Server 写法 #

UPDATE X1
SET X1.POPULATION = agg.total_pop
FROM X1
JOIN (
    SELECT X2.A, SUM(X3.POPULATION) AS total_pop
    FROM X2
    JOIN X3 ON X2.B = X3.B
    GROUP BY X2.A
) AS agg ON X1.A = agg.A;

处理无对应记录的情况：

UPDATE X1
SET X1.POPULATION = COALESCE(agg.total_pop, 0)
FROM X1
LEFT JOIN (
    SELECT X2.A, SUM(X3.POPULATION) AS total_pop
    FROM X2
    JOIN X3 ON X2.B = X3.B
    GROUP BY X2.A
) AS agg ON X1.A = agg.A;

这种方式无需临时表，所有逻辑都在单条语句内完成，你可以直接把这个模式套用到其他类似的表更新需求上。

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