我需要用子查询的结果更新新添加的列(存储发票所有项的和),子查询生成多个行。我在发票表中添加了新列。
ALTER TABLE Invoice
ADD ItemsSum int NULL我尝试了以下查询,但由于多个结果导致了错误
UPDATE Invoice
SET ItemsSum = (SELECT SUM(Amount)
FROM InvoiceItem it
INNER JOIN Invoice i ON it.InvoiceID = i.ID
GROUP BY i.ID)如何在Server中正确地实现这一点?
发布于 2020-02-18 09:07:56
您可以使用关联子查询:
UPDATE Invoice
SET ItemsSum = (SELECT SUM(it.Amount)
FROM InvoiceItem it
WHERE it.InvoiceID = Invoice.ID
);如果基础发票金额发生变化,ItemsSum将无效。
发布于 2020-02-18 12:21:39
相关的是:
UPDATE Invoice
SET ItemsSum = (SELECT SUM(invc.Amount)
FROM InvoiceItem invc
WHERE invc.InvoiceID = Invoice.ID
GROUP BY Invoice.ID;
);发布于 2020-02-18 12:38:32
Yogesh的答案是正确的,但如果您愿意,也可以使用显式聚合来完成:
UPDATE i
SET ItemsSum = ii.sumAmount
FROM Invoice i LEFT JOIN
(SELECT ii.InvoiceID, SUM(Amount) as sumAmount
FROM InvoiceItem it
GROUP BY ii.InvoiceID
) ii
ON ii.InvoiceID = i.IDhttps://stackoverflow.com/questions/60277197
复制相似问题