你的问题似乎比需要的复杂。我不认为分组按日期发布的子查询和按日期过滤两次的子查询有什么意义。
我认为你想要的逻辑是：

select count(p.post_id) post_count, d.fulldate
from dates d
left join posts p
    on  p.post_created_at >= d.fulldate
    and p.post_created_at <  d.fulldate + interval 1 day
where d.fulldate >= current_date - interval 1 month
group by d.fulldate
order by d.fulldate

我希望这应该比您原来的代码效率更高，特别是因为在post-date列上应用了not-date函数。我推荐一个关于 posts(post_created_at) 以充分利用此代码。

你需要做 IFNULL() 在主查询中，而不是在子查询中。子查询没有为缺少的日期返回任何行，因此甚至没有要转换为0的空计数。

SELECT IFNULL(posts_count, 0) AS posts_count, dates.fulldate
FROM dates
LEFT JOIN 
   (SELECT COUNT(*) AS posts_count, DATE_FORMAT(post_created_at, "%Y-%m-%d") AS pdate
   FROM posts
   WHERE DATE_FORMAT(post_created_at, "%Y-%m-%d") BETWEEN NOW() - INTERVAL 31 DAY AND NOW()
   GROUP BY DATE_FORMAT(post_created_at, "%Y-%m-%d")) t
ON t.pdate = dates.fulldate
WHERE dates.fulldate BETWEEN CURDATE() - INTERVAL 31 DAY AND CURDATE() 
ORDER BY dates.fulldate ASC;