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

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

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

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

就这三个。

select b.week_total,a.click_date,ifnull(b.count,0) as count
from (
    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 (
  select 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.click_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_format(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) use ($result, &$nums,&$total) {
            $index = array_search($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('count(*) 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))')
            ->select();
//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) use ($result, &$nums,&$total) {
            $index = array_search($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