我有一个简单的查询,可获取2个日期(一次约6个月)之间的所有已完成订单:
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行.
我需要它仍然返回6行,并将这些缺失的日期的金额设置为0.
这是仅返回5行的示例输出-缺少三月,我需要它出现:http://d.pr/DEuR
解决方法:
尝试使用coalesce函数,如果特定月份的sum(orders.amount)为null,它将返回“ 0”:
select COALESCE(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
标签:mysql
来源: https://codeday.me/bug/20191011/1890020.html