php+mysql获取7天、30天的统计数据，没有数值的补充0 By勤勤学长

需求：查找近7天的订单数量、订单金额以及他们的日期。

方法1：查到的大部分是这种写法。需要修改的地方

qqxz_orders 表名、
ctime 用作归类的字段我表中是时间戳，使用的时候需要用 FROM_UNIXTIME 格式化
total订单金额

就这三个。

lect b.week_total,a.click_date,ifnull(b.count,0) as countfrom (    SELECT curdate() as click_date    union all    SELECT date_sub(curdate(), interval 1 day) as click_date    union all    SELECT date_sub(curdate(), interval 2 day) as click_date    union all    SELECT date_sub(curdate(), interval 3 day) as click_date    union all    SELECT date_sub(curdate(), interval 4 day) as click_date    union all    SELECT date_sub(curdate(), interval 5 day) as click_date    union all    SELECT date_sub(curdate(), interval 6 day) as click_date) a left join (  lect date(FROM_UNIXTIME(ctime,'%Y%m%d')) as datetime, count(*) as count ,sum(total) as week_total  from qqxz_orders  group by date(FROM_UNIXTIME(ctime,'%Y%m%d'))) b on a.clic学习视频k_date = b.datetime

方法二：使用PHP处理
https://blog.csdn.net/umufeng/article/details/81046489（我是看这里知道这种写法的，方法一做不了30天的）

但是博主提供的代码中，没提到$scan_qushi的格式是什么样的，对我这种菜鸡来说，根本无法直接使用。研究了几个小时后终于给我整出来了，我还是以7天举例。

SELECT count(*) as count,date(FROM_UNIXTIME(ctime,"%Y%m%d")) as ctime,sum(total) as total FROM `qqxz_orders` WHERE  ( DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(from_unixtime(ctime)) ) GROUP BY date_f皮孔ormat(from_unixtime(ctime),'%Y%m%d')

获取到的数据是这样

预设最近七天的数值

        $day = 7;        for ($i = $day - 1; 0 <= $i; $i--) {            $result[] = date('Y-m-d', strtotime('-' . $i . ' day'));            $nums[] = 0;            $total[] = 0;        }

        array_walk($order_list_arr, function ($value, $key) u ($result, &$nums,&$total) {            $index = array_arch($value['ctime'],$result);            $nums[$index] = $value['count'];            $total[$index] = $value['total'];        });        $data = [            'day' => $result,            'nums' => $nums,            'total' =>$total        ];        halt($data);

打印看效果

完整代码

        $order_list = db('orders')            ->field('coun专科有学位证书吗t(*) as count,date(FROM_UNIXTIME(ctime,"%Y%m%d")) as ctime,sum(total) as total')           ->group("date_format(from_unixtime(ctime),'%Y%m%d')")           ->where('DATE_SUB(CURDATE(), INTERVAL 7 DAY) <琉璃钟= date(from_unixtime(ctime))')            ->lect();//sql语句//SELECT count(*) as count,date(FROM_UNIXTIME(ctime,"%Y%m%d")) as ctime,sum(total) as total FROM `qqxz_orders` WHERE  ( DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(from_unixtime(ctime))预备党员民主评议 ) GROUP BY date_format(from_unixtime(ctime),'%Y%m%d')                $order_list_arr = $order_list->toArray();        $day = 7;        for ($i = $day - 1; 0 <= $i; $i--) {            $result[] = date('Y-m-d', strtotime('-' . $i . ' day'));            $nums[] = 0;            $total[] = 0;        }        array_walk($order_list_arr, function ($value, $key) u ($result, &$nums,&$total) {            $index = array_arch($value['ctime'],$result);            $nums[$index] = $value['count'];            $total[$index] = $value['total'];        });        $data = [            'day' => $result,            'nums' => $nums,            'total' =>$total        ];        halt($data);

本文地址：https://blog.csdn.net/qq318692996/article/details/107304390