mysql：选择一个主题的评论和回复数

yyhrrdl8 于 2021-06-24 发布在 Mysql

关注(0)|答案(2)|浏览(334)

我有一个名为topics的表，用于存储论坛主题：

topic_id
topic
description
created_by
date_created
status

另一个表发布了我存储某个主题注解的位置：

post_id
topic_id
user_id
content
date_created

第三列是comments表，在这里我存储了一条评论的回复：

comment_id
topic_id
post_id
user_id
reply_to_id
comment
date_created

如何使用mysql语句计算每个主题的评论和回复的总数？
编辑：到目前为止，我试过这个，但我没有得到正确的数字

SELECT topics.topic_id,topics.topic, (COUNT(comments.topic_id)) AS count_comments 
FROM topics 
left join comments on topics.topic_id = comments.topic_id 
left join posts on topics.topic_id = posts.topic_id
WHERE topics.created_by != 'Admin' and topics.status = '1' 
GROUP by topics.topic_id

mysql php Html

来源：https://stackoverflow.com/questions/50007872/mysql-select-comments-and-the-number-of-replies-a-topic-have

2条答案

按热度按时间

sr4lhrrt1#

您可以尝试使用子查询来实现它：

SELECT `topics`.`topic_id`,
  (SELECT COUNT(*) FROM `comments` WHERE (`comments`.`topic_id`=`topics`.`topic_id`)) `comments_count`,
  (SELECT COUNT(*) FROM `posts` WHERE (`posts`.`topic_id`=`topics`.`topic_id`)) `posts_count`
FROM `topics`
WHERE (`topics`.`created_by` != 'Admin') AND (`topics`.`status` = '1')

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

zpf6vheq2#

SELECT   tpc.topic , 
         Count(cmt.comment) AS number of comments, 
         count(pst.content) AS number OF replies 
FROM     post pst, 
         comments cmt, 
         topics tpc 
GROUP BY tpc.topic_id

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

我来回答

mysql：选择一个主题的评论和回复数

2条答案

相关问题

热门标签

最新问答