mysql - 获取使用mysql的用户的最大评论数？

Question

我有这 4 张 table :

 posts{id,post,date}
    comment{id, user_id,post_id, comment, date}
    tag_post{tag_id,post_id}
    users{user_id, email,pwd,username}

我想要进行这个复杂的查询，我想从某个主题获取评论者(用户)的最大数量:即

  select the most commeneters(count) on posts that have been tagged with tag_id=39
LIMIT 5

谢谢:))

Answer 1

像这样的事情怎么样:

select users.user_id, count(*) as nb_comments
from posts
    inner join tag_posts on tag_posts.post_id = posts.id
    inner join comment on comment.post_id = posts.id
    inner join users on users.user_id = comment.user_id
where tag_posts.tag_id = 39
group by users.user_id
order by count(*) desc
limit 5

它应该为您提供对标签为 39 的帖子评论最多的 5 位用户。