mysql - 当另一个表上的行数为 0 时未获得结果

Question

我正在构建一个讨论板...我有一个包含帖子的表格和另一个包含评论的表格...外部 ID 是 post_id。现在，我正在尝试根据每个帖子的评论数量对帖子进行排序。我目前正在使用的查询:

SELECT username, posts.post_id, category_id, UNIX_TIMESTAMP(posts.datetime) AS datetime, posts.body, posts.owner_id, COUNT(comment_id) AS number
FROM posts, comments, user
WHERE posts.post_id = comments.post_id AND posts.owner_id = user.`id`
GROUP BY comments.post_id
ORDER BY number DESC 

查询工作正常，但问题是没有选择没有评论的帖子。我想显示帖子而不介意他们是否有评论，但我想按评论数量对它们进行排序。

Answer 1

使用左连接。您的 From/where 关节等同于 INNER JOIN :

SELECT username, p.post_id, category_id, UNIX_TIMESTAMP(p.datetime) AS datetime,
   p.body, p.owner_id, COUNT(comment_id) AS number
FROM posts p
LEFT JOIN comments c ON p.post_id = c.post_id
LEFT JOIN user u ON p.owner_id = u.`id`
GROUP BY c.post_id
ORDER BY number DESC