如何使用INNER JOIN和聚合函数更新表中指定字段？

错误根源 #

聚合函数（如SUM）不允许直接出现在UPDATE语句的SET赋值列表中，原写法未预先按购物车ID分组统计对应总金额，SQL无法识别每个订单对应的聚合计算维度。

修复写法 #

通用写法（适配SQL Server、PostgreSQL等） #

通过预聚合子查询先计算每个购物车的总金额，再关联订单表更新：

UPDATE O 
SET O.subtotal = ISNULL(C.cart_total, 0)
FROM Orders AS O
LEFT JOIN (
    SELECT 
        cart_id,
        SUM((price - discount_price) * qty) AS cart_total
    FROM Cart
    GROUP BY cart_id
) AS C ON O.cart_id = C.cart_id
WHERE O.date > '01/01/2021'

修改要点：

• 内层子查询按cart_id分组预先完成聚合计算，避免在SET层调用聚合函数
• 将原INNER JOIN改为LEFT JOIN，避免无对应购物车商品的订单被跳过更新，配合ISNULL将无商品订单的金额赋值为0，和原逻辑一致
• 建议将日期改为'2021-01-01'的标准格式，避免不同数据库的日期解析异常

CTE写法（可读性更高） #

WITH CartTotal AS (
    SELECT 
        cart_id,
        SUM((price - discount_price) * qty) AS cart_total
    FROM Cart
    GROUP BY cart_id
)
UPDATE O 
SET O.subtotal = ISNULL(CT.cart_total, 0)
FROM Orders AS O
LEFT JOIN CartTotal AS CT ON O.cart_id = CT.cart_id
WHERE O.date > '01/01/2021'

MySQL适配写法 #

MySQL语法略有差异，适配代码如下：

UPDATE Orders AS O
LEFT JOIN (
    SELECT 
        cart_id,
        SUM((price - discount_price) * qty) AS cart_total
    FROM Cart
    GROUP BY cart_id
) AS C ON O.cart_id = C.cart_id
SET O.subtotal = IFNULL(C.cart_total, 0)
WHERE O.date > '2021-01-01'

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