MySQL聚合是处理空值_MYSQL：在日期范围内聚合订单，其值为空值

Im trying to graph the sum of orders occuring for a particular date for the last 7 days but am having a problem when their aren't any orders for a particular date as the query isn't a value nor a date and therefore returning an array less than 7.

SELECT IFNULL(COUNT(*),0) as purchase_count, DATE(tb_order.order_date) as order_date

FROM tb_order_attendee_info

JOIN tb_order on tb_order.order_id = tb_order_attendee_info.order_id

JOIN tb_events on tb_events.event_id = tb_order.event_id

WHERE tb_order_attendee_info.refunded='N' AND order_date between DATE_SUB(now(), interval 10 day) and now()

GROUP BY DATE(tb_order.order_date)";

Ive been trying to work out how i can get it to return the array with zero values for purchase count and the incremented date, unfortunately its only returning a date when the count is greater than 0.

解决方案

You are trying to introduce result rows where there are none in the data. This is just not possible (without using a Stored Procedure). You cannot iterate over the dates as, so to say, there are no for loops in SQL.

The closest you can get is having/creating a (temporary) table of all the dates you want to have in the result and then JOIN to this table.