MySQL GROUP BY date - 如何在没有行时返回结果

发布于 2024-11-06 23:13:05 字数 496 浏览 4 评论 0原文

我正在编写一个查询来返回特定时间段内每天撰写的博客文章的数量。当某一天没有博客记录时，我的问题就出现了。通过我的查询，当天的结果完全被跳过。

这是我的查询：

SELECT DATE(`posted`), COUNT(`id`) 
    FROM `blogs` WHERE `status` = 'active' 
    && `posted` BETWEEN `2011-01-01` AND `2011-05-01` 
    GROUP BY DATE(`posted`)

它返回类似于以下内容的内容：

count | date
_________________
2     |  2011-01-01
5     |  2011-01-02
1     |  2011-01-04

注意，它缺少 2011-01-03，因为它没有任何帖子。

我如何让它显示那些帖子数为 0 的日子？

原文

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

海螺姑娘 2024-11-13 23:13:05

您需要有一个包含要查询的所有日期的表，并执行以下操作：

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`)

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`)

回复收藏 0 原文

吹梦到西洲 2024-11-13 23:13:05

创建包含日期的表（取自 http://www .artfulsoftware.com/infotree/queries.php?&bw=1280#95)：

-- Create a dummy view with 3 rows
create or replace view v3 as select 1 n union all select 1 union all select 1; 
-- create a second dummy view with 10 rows
-- By making joins with this view, you can create 100, 1000, ... rows
create or replace view v as select 1 n from v3 a, v3 b union all select 1; 
-- counter
set @n = 0; 
-- create date table
drop table if exists datetable; 
create table datetable(thedate date primary key); 
-- populate from start date
insert into datetable select cast('1970-1-1' + interval @n:=@n+1 day as date) as thedate 
from v a, v b, v c, v d, v e, v;

To create the table containing the dates (taken from http://www.artfulsoftware.com/infotree/queries.php?&bw=1280#95) :

-- Create a dummy view with 3 rows
create or replace view v3 as select 1 n union all select 1 union all select 1; 
-- create a second dummy view with 10 rows
-- By making joins with this view, you can create 100, 1000, ... rows
create or replace view v as select 1 n from v3 a, v3 b union all select 1; 
-- counter
set @n = 0; 
-- create date table
drop table if exists datetable; 
create table datetable(thedate date primary key); 
-- populate from start date
insert into datetable select cast('1970-1-1' + interval @n:=@n+1 day as date) as thedate 
from v a, v b, v c, v d, v e, v;

回复收藏 0 原文

~没有更多了~