Tp5按照日期合计查询出数据

示例如下

代码如下

$deviceId=$this->request->param('deviceId');
$date=$this->request->param('date',30);//
$end_time=time();
$begain_time=$end_time-$date*3600*24;
$option=[];
if($deviceId){
    $option['t.deviceId']=$deviceId;
}
if($date){
    $option['workDatetime']=['between',[$begain_time,$end_time]];
}
$deviceId_arr=Db::name('user_device')->where('user_id',$this->auth->id)->column('deviceId');
$option['t.deviceId']=['in',$deviceId_arr];
$arr['sum_sy']=Db::name('device_lists')
    ->alias('t')
    ->join('device_pl p','t.deviceId=p.deviceId')
    ->where($option)
    ->sum('p.sy');
$arr['sy']=Db::name('device_lists')
    ->alias('t')
    ->join('device_pl p','t.deviceId=p.deviceId')
    ->where($option)
    ->sum('p.sy');
$arr['list']=Db::name('device_lists')//此处是核心，用到了group和对日期格式化的操作
    ->alias('t')
    ->join('device_pl p','t.deviceId=p.deviceId')
    ->where($option)
    ->field('sum(p.sy) as sy,FROM_UNIXTIME(workDatetime,"%m-%d") as  workDatetime')
    ->group("DATE_FORMAT(FROM_UNIXTIME(workDatetime),'%Y-%m-%d')")
    ->select();
$this->success('成功',$arr);

本案例场景是查询指定日期范围内的设备的每日流量总和，得到绘制流量图的数据。

备注：如果MySQL大于5.7要小心不支持group，如果不支持则需要修改mysql.ini文件的配置

mysql.ini文件

在 [mysqld] 里加上

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

重启mysql即可