即使两个日期之间为空，也会生成空行 [英] generate empty rows even if empty between 2 dates

问题描述

我有一个简单的查询，可获取2个日期(一次约6个月)之间的所有已完成订单:

I have this simple query that fetches all completed orders between 2 dates(about 6 months at a time):

select IFNULL(sum(o.amount), 0) as amount, o.completed_at 
from orders o 
where date(o.completed_at) between '2011/10/01' and '2012/03/06' 
group by year(o.completed_at), month(o.completed_at) order by o.completed_at

但是这里的问题是例如1月和2月，如果没有订单，则在6个月内将只返回4行而不是6行.

But the problem here is for example in Jan and Feb, if there are no orders, then this will only return 4 rows instead of 6 rows for 6 months.

我需要它仍然返回6行，只是将那些缺少的日期的金额设置为0.

I need it still return 6 rows and just set the amount to 0 for those missing dates.

这里是仅返回5行的示例输出-缺少三月份，我需要它出现: http://d. pr/DEuR

Here is a sample output that only returns 5 rows - March is missing i need it to appear: http://d.pr/DEuR

推荐答案

这是您需要的:

1]创建一个辅助表

 create table db.myDates(
   completed_at date not null
 )

2]在表格中填写月份范围，例如:

2] fill the table with the months in range, for example:

 insert into db.myDates values('2011-10-01'),
 ('2011-11-01'),
 ('2011-12-01'),
 ('2012-01-01'),
 ('2012-02-01'),
 ('2012-03-01');

3]，然后选择:

 select a.completed_at, sum(b.amount)
 from myDates a
 left join orders b 
   on extract(year_month from a.completed_at)=extract(year_month from b.completed_at)
     and date(b.completed_at) between '2012-01-05' and '2012-06-05'
 group by year(b.completed_at)), month(dateCompleted)
 order by a.completed_at;

结果看起来像这样(日期是我的)

the result looks like this (dates mine)

2012-01-01  27
2012-02-01  NULL
2012-03-01  47
2012-04-01  13
2012-05-01  12
2012-06-01  15

这篇关于即使两个日期之间为空，也会生成空行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！