如何从三个表中计数？

pkwftd7m 于 2021-06-15 发布在 Mysql

关注(0)|答案(1)|浏览(322)

我有三张table post , post_like 以及 post_comment .
我想数数 likes 以及 comments 用户的帖子数量：
职位：

+-------------+--------------+------+-----+-------------------+----------------+
| 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  |     | ''                |                |

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

发布类似：

+---------+-------------+------+-----+---------+----------------+
| 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

问题是查询只返回一行，而有几个post。
我尝试过不同的技巧，也研究过类似的问题，但都找不到解决办法。
我该怎么修？

sql mysql Join mariadb

来源：https://stackoverflow.com/questions/54102062/how-to-count-from-three-tables

1条答案

按热度按时间

pxy2qtax1#

做你想做的事的快捷而肮脏的方法是使用 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;

更先进的方法是在你面前聚集 join ，但此方法可能适用于您的数据。

赞(0）回复(0）举报 2021-06-16

我来回答

如何从三个表中计数？

1条答案

相关问题

热门标签

最新问答