php - 如何通过连接计数在 mysql 中排序数据？

Question

我有两个表:

posts : id,title,content,show,created_at

comments: id,post_id,created_at

我正在尝试按评论最多的帖子排序。

SELECT *, COUNT(comments.id) AS total_comments
FROM comments LEFT JOIN posts ON posts.id = comments.post_id
WHERE posts.show = '1'
GROUP BY complains.id
ORDER BY total_comments DESC

问题是没有显示评论为 0 的帖子。任何帮助将不胜感激。

Answer 1

通过上面的加入，您错误地加入以获得有帖子的评论您应该像下面这样进行右连接或交换左连接中的表。

Select *, COUNT(comments.id) as total_comments
FROM posts
LEFT outer JOIN comments on  posts.id = comments.post_id
WHERE posts.show = '1'
GROUP BY posts.id
ORDER BY total_comments DESC