MySQL：从一个表中计数记录，然后更新另一个表

Question

有两个表/实体，场景非常简单。

poets 表 - 列: id、poet、nation

nations 表 - 列: id、nation、count

基本上，nations 到 poets 有一个自然的一对多映射。例如，有60个国家的1000位诗人。每个在 poets 中的诗人都被分配到一个国家，该国家通过 nation 字段包含在 nations 中的一个国家的 id 来表示。

nations 表中的 count 字段包含来自该国家的 poets 的数量。

我的问题是如何使用一个 SQL 查询来计算 poets 中每个国家的诗人数量，并更新该国家的相应计数？

我尝试了：

UPDATE poets, nations SET nations.count = COUNT(poets.id) GROUP BY poets.nation HAVING poets.nation = nations.id

但是它会给出#1064错误。尝试在某个地方组合WHERE子句，但它仍然无法工作。

有任何想法吗？

- datasn.io

Answer 1

使用子查询：

UPDATE nations 
   SET count = (
       SELECT COUNT(id) 
         FROM poets 
        WHERE poets.nation = nations.id 
        GROUP BY id
       );

- Zed

---

请注意，对于非常大的表格（500000条以上记录），这可能会变得非常缓慢。 - dusoft

此外，如果 nations.count 不可为空，可能会导致警告。使用 IFNULL 来防止这种情况发生。更新 nations 表，将 count 字段设置为 IFNULL((SELECT COUNT(id) FROM poets WHERE poets.nation = nations.id GROUP BY id),0)。 - Sam Barnum

我认为COUNT()函数不会返回NULL。如果没有找到任何行，它只会返回0。 - lucifurious

@dusoft 如果数据很大，有没有更好的方法可以使用其他表的计数进行更新？ - Baim Wrong

使用后台的cron job定期执行此操作，而不会影响您的网站速度。 - dusoft

Answer 2

你只需要不使用GROUP BY，参考以下内容：

UPDATE nations SET count = (
  SELECT COUNT(id) FROM poets 
    WHERE poets.nation = nations.id);

换句话说，使用GROUP BY时，对于没有诗人的国家，子查询将返回NULL。如果没有使用GROUP BY，则在这种情况下，子查询将返回0。

- mikhailian

---

null/0 在有/无 group by 的情况下的区别 - 这很有帮助。谢谢。 - Pradeep Kumar