本文介绍了MySQL GROUP BY日期 - 如何在没有行时返回结果的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我正在撰写一个查询以返回一段时间内每天写入的博文数量。当某一天没有博客记录时，我的问题就出现了。用我的查询，那一天的结果就完全被忽略了。
以下是我的查询：
SELECT DATE（`posted`） ，COUNT（`id`） FROM`blogs` WHERE`status` ='active'&& `posted'BETWEEN`2011-01-01` AND`2011-05-01` GROUP BY DATE（`posted`）它会返回类似于以下内容的内容：
count |日期 _________________ 2 | 2011-01-01 5 | 2011-01-02 1 | 2011-01-04请注意，由于它没有2011-01-03任何帖子。
如何让它显示那些有0个帖子的日子？
解决方案你需要有一个包含你要查询的所有日期的表， pre $ SELECT DATE（D.`thedate`），COUNT（`id`） FROM`datetable` D LEFT JOIN`blogs` B ON B.`posted` = D.`thedate` WHERE`status` ='active'&& D.`thedate` BETWEEN'2011-01-01` AND`2011-05-01` GROUP BY DATE（D.`thedate`）
I am writing a query to return the number of blog posts written per day over a certain period of time. My problem arises when there are no records of a blog for a given day. With my query, the result for that day is simply skipped altogether.
Here's my query:
SELECT DATE(`posted`), COUNT(`id`) FROM `blogs` WHERE `status` = 'active' && `posted` BETWEEN `2011-01-01` AND `2011-05-01` GROUP BY DATE(`posted`)It returns something similar to:
count | date _________________ 2 | 2011-01-01 5 | 2011-01-02 1 | 2011-01-04Notice that it is missing 2011-01-03 because it doesn't have any posts.
How do I get it to show those days with 0 posts?
解决方案You'd need to have a table that contains all the dates you're going to query over, and do something along the lines of...
SELECT DATE(D.`thedate`), COUNT(`id`) FROM `datetable` D LEFT JOIN `blogs` B ON B.`posted` = D.`thedate` WHERE `status` = 'active' && D.`thedate` BETWEEN `2011-01-01` AND `2011-05-01` GROUP BY DATE(D.`thedate`)
更多推荐
MySQL GROUP BY日期
发布评论