date:today,yesterday,-2 hours,-2 day,-2 week,-2 month,-2 year 今天，昨天，2小時內，2天內，2周內，2月內，2年內 date_type:%k,%j,%e,%w,%u,%c 小時(0……23)，天(001……366)，天(0.....31),天(0=星期日, 6=星期六),周(0……52)，月(1……12)  ``` $date_type=$param['date_type']; $data[1]=db('orders')->where(['shop_id'=>$param['shop_id']])->whereTime('create_time',$param['date']) ->field("sum(money_sum) as total,DATE_FORMAT(create_time,'$date_type') as time")->group("time")->select(); $data[2]=db('orders_unpay')->where(['shop_id'=>$param['shop_id']])->whereTime('create_time',$param['date']) ->field("sum(money_sum) as total,DATE_FORMAT(create_time,'$date_type') as time")->group("time")->select(); $data[3]=db('complaint')->alias('a')->leftJoin('orders b','a.order_id=b.order_id')->where(['a.shop_id'=>$param['shop_id']]) ->whereTime('a.create_time',$param['date']) ->field("sum(b.money_sum) as total,DATE_FORMAT(a.create_time,'$date_type') as time")->group("time")->select(); return show(0,'ok',$data); ``` 參考資料 ``` 按年匯總，統計： select sum(mymoney) as totalmoney, count(*) as sheets from mytable group by date_format(col, '%Y'); 按月匯總，統計：? select sum(mymoney) as totalmoney, count(*) as sheets from mytable group by date_format(col, '%Y-%m'); 按季度匯總，統計：? select sum(mymoney) as totalmoney,count(*) as sheets from mytable group by concat(date_format(col, '%Y'),FLOOR((date_format(col, '%m')+2)/3));? select sum(mymoney) as totalmoney,count(*) as sheets from mytable group by concat(date_format(col, '%Y'),FLOOR((date_format(col, '%m')+2)/3)); 按小時：? select sum(mymoney) as totalmoney,count(*) as sheets from mytable group by date_format(col, '%Y-%m-%d %H '); 查詢 本年度的數據: SELECT * FROM mytable WHERE year(FROM_UNIXTIME(my_time)) = year(curdate()) 查詢數據附帶季度數: SELECT id, quarter(FROM_UNIXTIME(my_time)) FROM mytable; 查詢 本季度的數據: SELECT * FROM mytable WHERE quarter(FROM_UNIXTIME(my_time)) = quarter(curdate()); 本月統計: select * from mytable where month(my_time1) = month(curdate()) and year(my_time2) = year(curdate()) 本周統計: select * from mytable where month(my_time1) = month(curdate()) and week(my_time2) = week(curdate()) N天內記錄: WHERE TO_DAYS(NOW())-TO_DAYS(時間字段)<=N ``` mysql函數地址 https://www.runoob.com/mysql/mysql-functions.html