MySQL 从子查询更新多列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16473402/
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 multiple columns from subquery
提问by kmoney12
This type of thing has been asked a few times before, but is not quite what I am looking for. I need to SETtwo rows equal to different parts of a subquery.
这种类型的事情之前已经被问过几次，但并不是我想要的。我需要SET两行等于子查询的不同部分。
I am currently using:
我目前正在使用：
UPDATE records
SET leads=(SELECT COUNT(*) FROM leads_table WHERE leads_table.blah=records.blah),
earnings=(SELECT SUM(amount) FROM leads_table WHERE leads_table.blah=records.blah)
The WHERE statements were obviously simplified...but basically its the same subquery but I don't think I should be running it twice?
WHERE 语句显然被简化了......但基本上它是相同的子查询，但我认为我不应该运行它两次？
I want to do something like...
我想做一些像...
UPDATE records
SET (leads,earnings)=(SELECT COUNT(*),SUM(amount) FROM leads_table WHERE leads_table.blah=records.blah)
回答by John Woo
You can simply join the table in a subquery that do some calculations,
您可以简单地将表加入进行一些计算的子查询中，
UPDATE records a
INNER JOIN
(
SELECT blah,
COUNT(*) totalCount,
SUM(amount) totalSum
FROM leads_table
GROUP BY blah
) b ON b.blah = a.blah
SET a.leads = b.totalCount
a.earnings = b.totalSum
