MySQL：从一个表中计算记录，然后更新另一个
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1216175/
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
MySQL: Count records from one table and then update another
提问by datasn.io
Got 2 tables / entities, very straightforward scenario.
有 2 个表/实体，非常简单的场景。
Table poets- Columns: id, poet, nation
表诗人- 列：id、poet、nation
Table nations- Columns: id, nation, count
表国家- 列：id、国家、计数
Basically, nationsto poetshas a mapping of one to many, naturally. For example, there are 1000 poets from 60 nations. Each poet in poetsis assigned to a nation by the nation field which contains the id of one of the nations in nations.
基本上，国家对诗人的映射自然是一对多。例如，有来自 60 个国家的 1000 位诗人。在每个诗人的诗人被包含在国家之一的id民族领域分配给一个民族国家。
The count field of nationscontains the number of poets in poetsfrom this nation.
国家的计数字段包含来自该国家的诗人中的诗人数量。
My question is how to use just one SQL query to count the number of poets by nation in poetsand then update the corresponding count of that nation?
我的问题是如何仅使用一个 SQL 查询来计算诗人中各个国家的诗人数量，然后更新该国家的相应数量？
I tried:
我试过：
UPDATE poets, nations SET nations.count = COUNT(poets.id) GROUP BY poets.nation HAVING poets.nation = nations.id
But it gives #1064 error. Also tried to combine WHERE clause somewhere but it still refuses to work.
但它给出了 #1064 错误。还尝试在某处组合 WHERE 子句，但它仍然拒绝工作。
Any idea?
任何的想法？
回答by Zed
Use a subquery:
使用子查询：
UPDATE nations
SET count = (
SELECT COUNT(id)
FROM poets
WHERE poets.nation = nations.id
GROUP BY id
);
回答by mikhailian
You just do not need GROUP BY, cf.:
你只是不需要 GROUP BY，参见：
UPDATE nations SET count = (
SELECT COUNT(id) FROM poets
WHERE poets.nation = nations.id);
Or rather, with GROUP BY, the subquery will return NULL for nations that have not poets. Without GROUP BY, the subquery will return 0 in this case.
或者更确切地说，使用 GROUP BY，子查询将为没有诗人的国家返回 NULL。如果没有 GROUP BY，子查询在这种情况下将返回 0。
