mysql - 无法通过计数相关表中的字段来更新表

我想通过计算followers表上的profile来更新followed_id表中的follow。

mysql> explain follow;
+-------------+---------+------+-----+---------+-------+
| Field       | Type    | Null | Key | Default | Extra |
+-------------+---------+------+-----+---------+-------+
| id          | int(11) | NO   |     | NULL    |       |
| followed_id | int(11) | NO   |     | NULL    |       |
| follower_id | int(11) | NO   |     | NULL    |       |
+-------------+---------+------+-----+---------+-------+

和

mysql> explain profile;
+----------------+---------------+------+-----+-------------------+----------------+
| Field          | Type          | Null | Key | Default           | Extra          |
+----------------+---------------+------+-----+-------------------+----------------+
| id             | int(11)       | NO   | PRI | NULL              | auto_increment |
| user_id        | int(10)       | NO   | MUL | 0                 |                |

| followers      | int(7)        | NO   |     | 0                 |                |
| following      | int(7)        | NO   |     | 0                 |                |
+----------------+---------------+------+-----+-------------------+----------------+

这是我想到的查询：

UPDATE profile A
INNER JOIN (SELECT id,COUNT(*) idcount FROM follow  GROUP BY id) as B
  ON B.id = A.user_id
SET A.followers = B.idcount

但是查询无法正常工作。当个人资料具有关注者时，仅添加1。

我怎样才能解决这个问题？

最佳答案

当前，您正在计算id中每个follow值的行数，该行数始终为1。您需要做的是计算每个follower_id的followed_id值数。另外，正如@juergend所指出的那样，您应该使用LEFT JOIN以便为没有关注者的用户获取0值。将查询更改为此：

UPDATE profile A
LEFT JOIN (SELECT followed_id, COUNT(DISTINCT follower_id) AS idcount
           FROM follow
           GROUP BY followed_id) as B ON B.followed_id = A.user_id
SET A.followers = COALESCE(B.idcount, 0)

您可以使用类似的查询来更新following：

UPDATE profile A
LEFT JOIN (SELECT follower_id, COUNT(DISTINCT followed_id) AS idcount
           FROM follow
           GROUP BY follower_id) as B ON B.follower_id = A.user_id
SET A.following = COALESCE(B.idcount, 0)