SQL 根据多个条件在另一个表中根据 SUM(values) 更新一个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13818472/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Update one table based upon SUM(values) in another table on multiple criteria
提问by BlueChippy
I can't seem to find out how to do this and not sure exactly how to search for it!
我似乎无法找到如何做到这一点，也不确定如何搜索它！
I have a table [MASTER]:
我有一张桌子[MASTER]：
ID varchar(6)
CCY varchar(3)
Val1 decimal(20,5)
Val2 decimal(20,5)
FOO decimal(20,5)
and another table [FOOS]
和另一张桌子 [FOOS]
ID varchar(6)
CCY varchar(3)
Val decimal(20,5)
MASTERcontains one row per ID/CCY composite key (not sure if thats correct term) e.g.
MASTER每个 ID/CCY 复合键包含一行（不确定这是否正确）例如
ABCDEF GBP 200.00 100.00 null
ABCDEF EUR 400.00 150.00 null
ZYXWVU GBP 300.00 200.00 null
ZYXWVU EUR 400.00 200.00 null
FOOScontains multiple rows and DOES NOT contain a row for every MASTERe.g.
FOOS包含多行并且不包含每个MASTER例如的行
ABCDEF GBP 50.00
ABCDEF GBP 51.00
ABCDEF GBP 150.00
ZYXWVU GBP 100.00
ZYXWVU EUR 200.00
ZYXWVU EUR 400.00
I'd like to run a query to update only matching MASTERrows with SUM(FOOS.Val). e.g.
我想运行一个查询来只更新匹配的MASTER行SUM(FOOS.Val)。例如
ABCDEF GBP 200.00 100.00 251.00
ABCDEF EUR 400.00 150.00 null
ZYXWVU GBP 300.00 200.00 100.00
ZYXWVU EUR 400.00 200.00 600.00
...but although I've tried a numer of options (where exists, inner join) I can't seem to be able to either link to a single MASTERor do the SUM(...)
...但是尽管我尝试了许多选项 ( where exists, inner join) 我似乎无法链接到单个MASTER或执行SUM(...)
回答by Mahmoud Gamal
Try this solution:
试试这个解决方案：
UPDATE m
SET m.Foo = f.valsum
FROM [MASTER] m
INNER JOIN
(
SELECT ID, CCY, SUM(val) valsum
FROM Foos
GROUP BY ID, CCY
) f ON m.ID = f.ID AND m.CCY = f.CCY;
回答by Yoric
With postgres, I had to adjust the solution with this to work for me:
使用 postgres，我不得不调整解决方案来对我来说有效：
UPDATE [MASTER] m
SET Foo = f.valsum
FROM
(
SELECT ID, CCY, SUM(val) valsum
FROM Foos
GROUP BY ID, CCY
) f
WHERE m.ID = f.ID AND m.CCY = f.CCY;
回答by Xuan l
UPDATE accounts SET (contact_last_name, contact_first_name) = (SELECT last_name, first_name FROM salesmen WHERE salesmen.id = accounts.sales_id);
更新帐户 SET (contact_last_name, contact_first_name) = (SELECT last_name, first_name FROM salesmen WHERE salesmen.id = accounts.sales_id);
update orders set amount = (select sum(item_quantity) from order_items where orders.id = order_items.order_id);
update orders set amount = (select sum(item_quantity) from order_items where orders.id = order_items.order_id);
