mysql - 在mysql数据集中添加缺失的日期

Question

我有一个像这样的 mysql 查询....

   SELECT CAST(created_at AS DATE) as 'Created_Date', DAYNAME(CAST(created_at AS DATE)) as 'Day', sum(order_type_id=1) as 'Pickup', sum(order_type_id=2) as 'Delivery'
   FROM orders
   WHERE created_at >= curdate() - INTERVAL 7 DAY
   AND created_at < curdate()
   AND order_status_id != 3
   group by CAST(created_at AS DATE);

返回一些数据如下

    Date   |      Day     | Pickup | Delivery
-----------------------------------------------
2017-08-08 |    Tuesday   |  02    |    01    
2017-08-09 |   Wednesday  |  01    |    01    
2017-08-10 |    Thursday  |  01    |    00  
2017-08-11 |    Friday    |  01    |    01   
2017-08-13 |    Sunday    |  01    |    00  
2017-08-14 |    Monday    |  01    |    01     

我想做的是获取过去 7 天(不包括今天)的送货订单和取货订单的摘要。

我的问题:如果您仔细观察上面的内容，您会注意到我没有 2017 年 8 月 12 日(星期六)的输出，因为同一日期没有执行任何操作。知道如何将这些空日期输入到我的输出中，这样我就会得到如下所示的内容？？？

    Date   |      Day     | Pickup | Delivery
-----------------------------------------------
2017-08-08 |    Tuesday   |  02    |    01    
2017-08-09 |   Wednesday  |  01    |    01    
2017-08-10 |    Thursday  |  01    |    00  
2017-08-11 |    Friday    |  01    |    01   
2017-08-12 |   Saturday   |  00    |    00  
2017-08-13 |    Sunday    |  01    |    00  
2017-08-14 |    Monday    |  01    |    01     

Answer 1

如果您实际上拥有所有日期的数据(只是不是您想要的该日期的状态)，那么您可以使用条件聚合:

   select CAST(created_at AS DATE) as Created_Date,
          DAYNAME(CAST(created_at AS DATE)) as Day,
          sum(order_type_id = 1 and order_status_id <> 3) as Pickup,
          sum(order_type_id = 2 and order_status_id <> 3) as Delivery
   from orders
   where created_at >= curdate() - INTERVAL 7 DAY AND created_at < curdate()
   group by CAST(created_at AS DATE);

如果情况并非如此，那么您需要一个表格，其中每个日期一行。最简单的方法是日历表。