mysql - 如何从三个表中计数？

Question

我有 3 个表 post、post_like 和 post_comment。

我想计算用户帖子的喜欢和评论:

帖子:

+-------------+--------------+------+-----+-------------------+----------------+
| Field       | Type         | Null | Key | Default           | Extra          |
+-------------+--------------+------+-----+-------------------+----------------+
| id          | int(30)      | NO   | PRI | NULL              | auto_increment |
| user_id     | int(11)      | NO   |     | 0                 |                |
| description | text         | YES  |     | NULL              |                |
| link        | varchar(100) | YES  |     | ''                |                |

+-------------+--------------+------+-----+-------------------+----------------+

post_like:

+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| id      | int(11)     | NO   | PRI | NULL    | auto_increment |
| post_id | int(30)     | NO   |     | 0       |                |
| user_id | int(30)     | NO   |     | 0       |                |
| time    | varchar(50) | NO   |     | 0       |                |
+---------+-------------+------+-----+---------+----------------+

发表评论:

+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int(30)      | NO   | PRI | NULL    | auto_increment |
| post_id | int(20)      | NO   |     | 0       |                |
| user_id | int(20)      | NO   |     | 0       |                |
| text    | text         | YES  |     | NULL    |                |
| time    | varchar(100) | NO   |     | 0       |                |
+---------+--------------+------+-----+---------+----------------+

这是我提出的查询:

SELECT 
    p.*,
    COUNT(l.post_id) "likes", 
    COUNT(c.post_id) "comments"
FROM 
    post p
    INNER JOIN post_like l ON p.id = l.post_id 
    INNER JOIN post_comment c ON c.post_id = l.post_id
WHERE 
    p.user_id=55 
GROUP BY 
    l.post_id
ORDER BY
    p.created_at DESC

问题是查询只返回一行，而有几个帖子。

我尝试了不同的技巧并查看了类似的问题，但找不到解决方案。

我该如何解决？

Answer 1

做你想做的事情的快速而肮脏的方法是使用count(distinct):

SELECT p.id, p.user_id, p.description, p.link,
       COUNT(DISTINCT l.id) as num_likes, 
       COUNT(DISTINCT c.id) as num_comments
FROM post p LEFT JOIN
     post_like l
     ON p.id = l.post_id LEFT JOIN
     post_comment c
     ON p.id = c.post_id
WHERE p.user_id=55 
GROUP BY p.id, p.user_id, p.description, p.link
ORDER BY p.created_at DESC;

更高级的方法是在您加入之前进行聚合，但这种方法可能适用于您的数据。