mysql连接四个桌子，其中两个只有计数

Question

我想加入4个表，这些是表

+-----------------------------------------+
|                  User                   |
+----+----------+----------+-------+------+
| id | username | password | email | name |
+----+----------+----------+-------+------+
+----------------------------------+
|               Post               |
+----+--------+-------------+------+
| id | userID | description | time |
+----+--------+-------------+------+
+------------------------------------+
|               Comment              |
+----+--------+--------+------+------+
| id | userID | postID | text | time |
+----+--------+--------+------+------+
+----------------------+
|         Love         |
+----+--------+--------+
| id | userID | postID |
+----+--------+--------+

我想向所有用户展示所有用户的评论和"爱"标记的帖子。这是我现在的查询，但是它返回CountComment和CountLove的不良值：

SELECT User.id AS userID, User.username, User.name, Post.id AS postID, Post.description, Post.time, COUNT(Comment.id) AS countComment, COUNT(Love.id) as countLove
FROM User
    JOIN Post ON User.id = Post.userID
    LEFT JOIN Comment ON Comment.postID = Post.id
    LEFT JOIN Love ON Love.postID = Post.id
GROUP BY Post.id
ORDER BY User.id ASC, Post.time DESC

我想看的字段：

+--------+----------+------+--------+-------------+------+--------------+-----------+
| userID | username | name | postID | description | time | countComment | countLove |
+--------+----------+------+--------+-------------+------+--------------+-----------+

谢谢大家的帮助，我非常感谢。

Answer 1

查询是按post.id分组的。看起来也需要包括用户。

group by user.id, post.id

Answer 2

我设法使用此查询做我想做的事情：

SELECT
    User.id AS userID, User.username, User.name,
    Post.id AS postID, Post.description, Post.time,
    COALESCE(c.count, 0) AS countComment, COALESCE(l.count, 0) AS countLove
FROM User
    JOIN Post ON User.id = Post.userID
    LEFT JOIN (SELECT Comment.postID, COUNT(*) AS count FROM Comment GROUP BY Comment.postID) c ON c.postID = Post.id
    LEFT JOIN (SELECT Love.postID, COUNT(*) AS count FROM Love GROUP BY Love.postID) l ON l.postID = Post.id
ORDER BY User.id ASC, Post.id DESC