如何在 join 语句中计数 [英] How to count in a join statement

问题描述

我有表格 post: int post_id, varchar title, text content
表 comment:int comment_id, int post_id, varchar content 其中 post_id 是外键引用表 post.

I have got table post: int post_id, varchar title, text content
And table comment:int comment_id, int post_id, varchar content where post_id is a foreign key references table post.

我如何通过评论计数获得每个帖子订单的 post_id 和评论总数.谢谢.

How do i get the post_id and sum of comments of each post order by comments count. Thank you.

推荐答案

如果你想要没有评论的帖子:

If you want posts that have no comments:

SELECT
    post.post_id,
    --post.title,
    --post.content,
    COUNT(comment.post_id) AS comment_count
FROM post
LEFT JOIN comment ON post.post_id = comment.post_id
GROUP BY post.post_id
ORDER BY comment_count DESC

(此查询使用 MySQLs GROUP BY带有隐藏列 扩展名).

(This query uses the MySQLs GROUP BY with hidden columns extension).

如果您不想要没有评论的帖子，您可以使用更简单的查询:

If you don't want posts that have no comments you can use a simpler query:

SELECT post_id, COUNT(*) AS comment_count
FROM comment
GROUP BY post_id
ORDER BY comment_count DESC

这篇关于如何在 join 语句中计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！