MySQL:即使没有记录也选择一个范围内的所有日期

Question

我有一个用户数据库。我想创建一个基于用户群增长的图表。我现在的查询是:

SELECT DATE(datecreated), count(*) AS number FROM users 
WHERE DATE(datecreated) > '2009-06-21' AND DATE(datecreated) <= DATE(NOW())
GROUP BY DATE(datecreated) ORDER BY datecreated ASC

这返回几乎我想要的。如果我们某一天有 0 个用户，那一天不会作为 0 值返回，它只是被跳过并返回至少有一个用户的第二天。我怎样才能得到类似(伪响应)的东西:

date1 5
date2 8
date3 0
date4 0
date5 9
etc...

带零的日期与其余日期按顺序显示在哪里？

谢谢!

Answer 1

我希望你能解决剩下的问题。

select  * from (
select date_add('2003-01-01 00:00:00.000', INTERVAL n5.num*10000+n4.num*1000+n3.num*100+n2.num*10+n1.num DAY ) as date from
(select 0 as num
   union all select 1
   union all select 2
   union all select 3
   union all select 4
   union all select 5
   union all select 6
   union all select 7
   union all select 8
   union all select 9) n1,
(select 0 as num
   union all select 1
   union all select 2
   union all select 3
   union all select 4
   union all select 5
   union all select 6
   union all select 7
   union all select 8
   union all select 9) n2,
(select 0 as num
   union all select 1
   union all select 2
   union all select 3
   union all select 4
   union all select 5
   union all select 6
   union all select 7
   union all select 8
   union all select 9) n3,
(select 0 as num
   union all select 1
   union all select 2
   union all select 3
   union all select 4
   union all select 5
   union all select 6
   union all select 7
   union all select 8
   union all select 9) n4,
(select 0 as num
   union all select 1
   union all select 2
   union all select 3
   union all select 4
   union all select 5
   union all select 6
   union all select 7
   union all select 8
   union all select 9) n5
) a
where date >'2011-01-02 00:00:00.000' and date < NOW()
order by date

与

select n3.num*100+n2.num*10+n1.num as date

你会得到一列数字从 0 到 max(n3)*100+max(n2)*10+max(n1)

因为这里我们的最大 n3 为 3，SELECT 将返回 399，加上 0 -> 400 条记录(日历中的日期)。

您可以通过限制来调整您的动态日历，例如，您必须从 min(date) 到 now()。