SQL 更新查询中的聚合函数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2009927/
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
Aggregate function in an SQL update query?
提问by Margaret
I'm trying to set the value in one table to the sum of the values in another table. Something along these lines:
我试图将一个表中的值设置为另一个表中值的总和。沿着这些路线的东西：
UPDATE table1
SET field1 = SUM(table2.field2)
FROM table1
INNER JOIN table2 ON table1.field3 = table2.field3
GROUP BY table1.field3
Of course, as this stands, it won't work - SETdoesn't support SUMand it doesn't support GROUP BY.
当然，就目前而言，它不起作用 -SET不支持SUM也不支持GROUP BY.
I should know this, but my mind's drawing a blank. What am I doing wrong?
我应该知道这一点，但我的脑海里一片空白。我究竟做错了什么？
回答by JBrooks
UPDATE t1
SET t1.field1 = t2.field2Sum
FROM table1 t1
INNER JOIN (select field3, sum(field2) as field2Sum
from table2
group by field3) as t2
on t2.field3 = t1.field3
回答by OMG Ponies
Use:
用：
UPDATE table1
SET field1 = (SELECT SUM(t2.field2)
FROM TABLE2 t2
WHERE t2.field3 = field2)
回答by Paulo Santos
Or you could use a mix of JBrooksand OMG Poniesanswers:
或者你可以混合使用JBrooks和OMG Ponies 的答案：
UPDATE table1
SET field1 = (SELECT SUM(field2)
FROM table2 AS t2
WHERE t2.field3 = t1.field3)
FROM table1 AS t1
回答by Jonathan Roberts
A good situation to use CROSS APPLY
使用 CROSS APPLY 的好情况
UPDATE t1
SET t1.field1 = t2.field2Sum
FROM table1 t1
CROSS APPLY (SELECT SUM(field2) as field2Sum
FROM table2 t2
WHERE t2.field3 = t1.field3) AS t2
回答by Bludwarf
I know the question is tagged SQL Server but be careful with UPDATE with JOIN if you are using PostgreSQL. @JBrooks answer won't work :
我知道这个问题被标记为 SQL Server 但如果您使用的是PostgreSQL ，请小心使用 JOIN 进行 UPDATE 。@JBrooks 回答不起作用：
UPDATE t1
SET t1.field1 = t2.field2Sum
FROM table1 t1
INNER JOIN (...) as t2
on t2.field3 = t1.field3
You will have to adapt it to :
您将不得不使其适应：
UPDATE table1 t1
SET t1.field1 = t2.field2Sum
FROM (...) as t2
WHERE t2.field3 = t1.field3
See parameter from_listin the doc to get why FROMis considered by PostgreSQL as a self-join : https://www.postgresql.org/docs/9.5/static/sql-update.html#AEN89239
请参阅from_list文档中的参数以了解为什么FROMPostgreSQL 将其视为自联接：https: //www.postgresql.org/docs/9.5/static/sql-update.html#AEN89239
回答by Karan
You can also use CTE like below.
您也可以像下面那样使用 CTE。
;WITH t2 AS (
SELECT field3, SUM(field2) AS field2
FROM table2
GROUP BY field3
)
UPDATE table1
SET table1.field1 = t2.field2
FROM table1
INNER JOIN t2 ON table1.field3 = t2.field3
