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.
效果图：