我正在撰写一个查询以返回一段时间内每天写入的博文数量。当某一天没有博客记录时,我的问题就出现了。用我的查询,那一天的结果就完全被忽略了。
以下是我的查询:
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日期
发布评论