mysql 左连接和查询，where in 子句仅显示 1 行

Question

由于我正在制作一个简单的论坛，带有像关注者和关注者这样的 Twitter 触摸，因此我有下表。

users

 1. id
 2. name

questions

 1. id
 2. description
 3. user_id//foreign key of users.id

answers

 1. id
 2. question_id//foreign key of questions.id
 3. description
 4. user_id//foreign key of users.id

follow

 1. from_user(which user)//foreign key of users.id
 2. to_user(following to which user)//foreign key of users.id

我的查询如下

SELECT users.id, users.name, count(DISTINCT questions.id), count(answers questions.id), count(follow1.from_user), count(follow2.to_user) from users LEFT JOIN questions ON users.id = questions.user_id LEFT JOIN answers ON users.id = answers.user_id LEFT JOIN follow as follow1 ON users.id = follow1.from_user LEFT JOIN follow as follow2 ON users.id = follow2.to_user WHERE users.id = 1

这工作正常并给出正确的结果。

但是当使用相同的查询时，我将 WHERE IN 子句如下所示

SELECT users.id, users.name, count(DISTINCT questions.id), count(answers questions.id), count(follow1.from_user), count(follow2.to_user) from users LEFT JOIN questions ON users.id = questions.user_id LEFT JOIN answers ON users.id = answers.user_id LEFT JOIN follow as follow1 ON users.id = follow1.from_user LEFT JOIN follow as follow2 ON users.id = follow2.to_user WHERE users.id IN (1, 3)

它仅显示用户 id 1 的记录。不显示用户 id 3 的记录。但是谈论之前的查询对于 id 都适用，并显示正确的记录。

Answer 1

这是一个奇怪的问题，我只能建议使用GROUP BY users.id，这样mysql就不会合并所有记录的count()。希望能在某种程度上有所帮助。