用同一表计算值更新表的最佳方法

Question

我完全是sql方面的新手。我认为最好的方法是:我有收据表，上面有几个字段：

Receipt
-------
ReceiptID, AssociatedReceiptID, Value, Total

我想用相同的AssociatedReceiptID和值字段更新所有行的总计字段。因此，我尝试了下一个sql语句：

UPDATE Receipt r1
SET Total = (SELECT sum(Value)
             FROM Receipt r2
             WHERE r2.AssociatedReceiptID = r1.AssociatedReceiptID
             GROUP BY r2.AssociatedReceiptID)

这张桌子上有100000多条记录，持续时间超过17个小时。由于我正在更新我所请求的同一个表，所以我决定将它拆分为两个更新语句，将sum结果存储在一个临时表中(其中有)，然后用这些值更新接收表。

UPDATE TemporaryTable t1
SET Total = (SELECT sum(Value)
             FROM Receipt r2
             WHERE r2.AssociatedReceiptID = t1.AssociatedReceiptID
             GROUP BY r2.AssociatedReceiptID)

UPDATE Receipt r1
SET Total = (SELECT Total
             FROM TemporaryTable t1
             WHERE t1.ReceiptID = r1.ReceiptID)

使用这些语句，更新过程需要6-7小时。但我相信应该有更好的方法。因此，简单来说，以下是我的问题：

• 你怎么才能用更好的方式做这件事？
• update语句中的子查询每行更新一次，不是吗？因此，如果有10行与相同的AssociatedReceiptID，和计算10倍。如何在update语句中只计算每个AssociatedReceiptID一次的总和？

提前谢谢。

Answer 1

尝试在内存中创建一个临时表：

DECLARE @temp_receipts TABLE (
AssociatedReceiptID int,
sum_value int)

然后：

insert into @temp_receipts
SELECT AssociatedReceiptID, sum(Value)
FROM Receipt
GROUP BY AssociatedReceiptID

然后更新主表总数：

UPDATE Receipt r
SET Total = (SELECT sum_value
             FROM @temp_receipts tt
             WHERE r.AssociatedReceiptID = tt.AssociatedReceiptID)

但是，我会创建一个名为receipt_totals或什么的表，然后使用它。在每个相关行中包含每个相关收据的总数是没有意义的。如果您这样做是为了查询方便，请考虑在收据和receipt_totals之间创建一个视图。

Answer 2

最初，你可以按照你在问题中建议的方式去做。

对于一行的每次更改，我认为最好使用数据库触发器。它们将更新每一行的值。

您可以在MySQL 这里中阅读有关触发器的内容。

您可能需要使用InnoDB作为存储引擎。

如果您不使用MySQL，请检查相应的参考数据库。

Answer 3

MERGE INTO Receipt r
USING (
        SELECT sum(Value) s, AssociatedReceiptID
        FROM Receipt           
        GROUP BY AssociatedReceiptID
      ) r_sum 
ON( r.AssociatedReceiptID = r_sum.AssociatedReceiptID)
WHEN MATCHED THEN UPDATE
set r.Total = r_sum.s
;