MySQL GROUP BY日期 - 如何在没有行时返回结果 [英] MySQL GROUP BY date - how to return results when no rows

问题描述

我正在撰写一个查询以返回一段时间内每天写入的博文数量。当某一天没有博客记录时，我的问题就出现了。用我的查询，那一天的结果就完全被忽略了。

以下是我的查询：

  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-04

Notice 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日期 - 如何在没有行时返回结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！