php mysql 如何获取最近一周7天，每天的订单数？

Question

问题一：
1、$array数组如下：

array (size=7)
  1 => string '2018-09-21' (length=10)
  2 => string '2018-09-22' (length=10)
  3 => string '2018-09-23' (length=10)
  4 => string '2018-09-24' (length=10)
  5 => string '2018-09-25' (length=10)
  6 => string '2018-09-26' (length=10)
  7 => string '2018-09-27' (length=10)

2、$list数组如下：

array (size=3)
  0 => 
    array (size=2)
      'riqi' => string '2018-09-21' (length=10)
      'count' => int 2
  1 => 
    array (size=2)
      'riqi' => string '2018-09-26' (length=10)
      'count' => int 2
  2 => 
    array (size=2)
      'riqi' => string '2018-09-27' (length=10)
      'count' => int 3

求助：如何php实现以下效果

array (size=3)
  0 => 
    array (size=2)
      'riqi' => string '2018-09-21' (length=10)
      'count' => int 2
  1 => 
    array (size=2)
      'riqi' => string '2018-09-22' (length=10)
      'count' => int 0
  2 => 
    array (size=2)
      'riqi' => string '2018-09-23' (length=10)
      'count' => int 0
  3 => 
    array (size=2)
      'riqi' => string '2018-09-24' (length=10)
      'count' => int 0
  4 => 
    array (size=2)
      'riqi' => string '2018-09-25' (length=10)
      'count' => int 0
  5 => 
    array (size=2)
      'riqi' => string '2018-09-26' (length=10)
      'count' => int 2
  6 => 
    array (size=2)
      'riqi' => string '2018-09-27' (length=10)
      'count' => int 3

---

问题二：
现有sql如下：

select FROM_UNIXTIME(addtime,'%Y-%m-%d') as riqi, count(1) as count from order where FROM_UNIXTIME(addtime,'%Y-%m-%d') >= now() - interval 7 day group by FROM_UNIXTIME(addtime,'%Y-%m-%d')

如何能通过一句sql实现以下效果

array (size=3)
  0 => 
    array (size=2)
      'riqi' => string '2018-09-21' (length=10)
      'count' => int 2
  1 => 
    array (size=2)
      'riqi' => string '2018-09-22' (length=10)
      'count' => int 0
  2 => 
    array (size=2)
      'riqi' => string '2018-09-23' (length=10)
      'count' => int 0
  3 => 
    array (size=2)
      'riqi' => string '2018-09-24' (length=10)
      'count' => int 0
  4 => 
    array (size=2)
      'riqi' => string '2018-09-25' (length=10)
      'count' => int 0
  5 => 
    array (size=2)
      'riqi' => string '2018-09-26' (length=10)
      'count' => int 2
  6 => 
    array (size=2)
      'riqi' => string '2018-09-27' (length=10)
      'count' => int 3

Answer 1

目的:实现数据的组装; 1.先循环日期数组2.每一个循环代码内,调用函数或方法3.上面的函数或方法就是实现根据日期查询数据,返回订单数的结果

Answer 2

SELECT FROM_UNIXTIME(addtime,'%Y-%m-%d') as riqi,count(*) from order GROUP BY riqi ORDER BY riqi LIMIT 7应该是你要的结果

Answer 3

用to_days(now()) - to_days(addtime)<= 7来查取近7天的数据，然后用date方法将时间只保留到天数。再对查到的数据，根据时间进行分组，再count(订单)，得到就是每天对应的订单数了。如果要把订单数为0 的天数也展示出来，就写个日期的循环，判断每天的日期是否在查询结果里有对应的记录，有就直接取，没有就是0.效果图：