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

最新推荐文章于 2021-02-18 06:41:59 发布

转载最新推荐文章于 2021-02-18 06:41:59 发布 · 133 阅读

文章标签：

本文解决了一个SQL查询问题，即如何为特定日期的前7天生成订单汇总数据，并正确处理无订单日期的情况。通过讨论，明确了SQL无法直接迭代日期，而需要借助临时表或存储过程来实现。

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.