使用此查询：

select p.post_id, count(c.id) as total_comments
from posts p LEFT JOIN comments c
on p.post_id = c.id
group by p.post_id
order by p.post_id desc;

插图：

select * from posts;
+----------+---------+
| postdesc | post_id |
+----------+---------+
| post-1   |       1 |
| post-2   |       2 |
| post-3   |       3 |
+----------+---------+

select * from comments;
+--------------+------+------------+
| comment_desc | id   | created_at |
+--------------+------+------------+
| comment-1    |    1 | 2018-04-01 |
| comment-2    |    1 | 2018-04-02 |
| comment-3    |    2 | 2018-04-03 |
| comment-4    |    1 | 2018-04-04 |
+--------------+------+------------+

--query
+---------+----------------+
| post_id | total_comments |
+---------+----------------+
|       3 |              0 |
|       2 |              1 |
|       1 |              3 |
+---------+----------------+

更新：这里有一个不使用连接语法的版本。给出与上述相同的结果。

select a.post_id, a.total_comments 
from
(
    select post_id, 0 as total_comments
    from posts
    where post_id not in 
        (
            select distinct id 
            from comments
        )
    union
    select id, count(*)
    from comments
    group by id
) a
order by a.post_id desc;

试试这个：

SELECT post_id, COUNT(post_id)
FROM comment
WHERE post_id IN (1, 2, 3, 4, 5)
GROUP BY post_id
HAVING COUNT(post_id) >= 0 
ORDER BY created_at DESC, post_id DESC

因为它是空的
看看这个

SELECT COUNT(post_id) AS 'count'
FROM posts LEFT JOIN comments on post.id = comments.post_id
WHERE post.id IN (1, 2, 3, 4, 5) 
GROUP BY post_id ORDER BY count DESC, created_at DESC

你需要从 posts 表中，有些帖子没有评论，所以 post_id 不存在于 comments

Having 在这种情况下没有帮助。看起来你对某些帖子没有任何评论。所以，您将不会从comments表中为这篇文章选择任何记录。你需要这样的东西：

select p.id, count(c.id)
from posts p 
left join comments c on p.id = c.post_id
where p.id in (1, 2, 3, 4, 5) 
GROUP BY p.id
ORDER BY p.id DESC