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

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个多小时。由于我正在更新正在查询的同一个表，因此我决定将其拆分为两个更新语句，将总和结果存储在临时表（其中有 ）中，然后使用这些值更新收据表。

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 小时。但我确信应该有更好的方法来做到这一点。简而言之，这些是我的问题：

• 你如何以更好的方式做到这一点？
• 更新语句中的子查询每更新一行就执行一次，不是吗？因此，如果有 10 行具有相同的 AssociatedReceiptID，则总和将计算 10 次。如何在更新语句中为每个 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或其他内容的表并使用它。在每个相关行中包含每个相关收据的总计是没有意义的。如果您这样做是为了查询方便，请考虑在receipts和receipt_totals之间创建一个视图

Answer 2

最初，您可以按照问题中建议的方式进行。

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

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

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

如果您不使用 MySQL，请检查与您的 DBMS 相对应的参考资料。

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
;

Answer 4

我知道这是一个老问题，但我认为更好的方法是这样。

UPDATE r1
    SET r1.Total = r2.sumValue

FROM Receipt r1
    INNER JOIN 
        (SELECT sum(Value) sumValue,AssociatedReceiptID
        FROM Receipt rSum
        GROUP BY rSum.AssociatedReceiptID) r2 ON r2.AssociatedReceiptID = r1.AssociatedReceiptID

这就是一次查询和计算的全部内容。

希望它有用。

Answer 5

对于大型表，将表复制到新表（并同时进行更改）比更新它要快得多（至少在 Oracle DB 上）。

例如： 比

update table1 set some_num = some_num +1 where year = 2010;

：（

create table table1b as
  select (case when year = 2010 then some_num+1 else some_num) as some_num,
      other, columns, of, the, table
  from table1;
drop table1;
rename table1b to table1; -- also fix or recreate constraints

从表中删除行也是如此：将应保留的所有行复制到新表中，然后重命名它，而不是在原始表上进行常规 DELETE）

所以在您的情况下将会是：

create table ReceiptNew as
    select ReceiptID, AssociatedReceiptID, Value,
        sum(value) over (partition by AssociatedReceiptID) 
        as Total
    from Receipt;

drop table Receipt;
rename ReceiptNew to Receipt;

同样，您必须重新设置表上的约束（“NOT NULL”除外，它们会自动继承）。