MYSQL：在日期范围内聚合订单，其值为空值 [英] MYSQL : Aggregating orders across date range with null values for count

问题描述

我试图绘制最近7天的某个特定日期发生的订单总额，但是由于查询不是值和日期，因此在特定日期没有任何订单时出现问题，因此返回一个小于7的数组。

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)"; 

我一直在努力解决我如何让它以零的价值返回数组，以获取购买数量和增加的日期，不幸的是，它只返回一个日期，当计数是grea

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.

推荐答案

您正在尝试引入数据中没有的结果行。这是不可能的（不使用存储过程 ）。您不能在日期中迭代，所以说，SQL中没有循环的。

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.

这篇关于MYSQL：在日期范围内聚合订单，其值为空值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！