为什么聚合函数在我的laravelsql查询中不起作用?

p3rjfoxz  于 2021-06-18  发布在  Mysql
关注(0)|答案(2)|浏览(487)
$year = date("y"); 
for($i=1;$i<=12;$i++) 
{ 
   $MonthlyReceive = DB::table('order_items') ->whereBetween('created_at',array($year.'-'.$i.'-1',$year.'-'.$i.'-31')) ->select(DB::raw('sum(price*quantity)'))->where('quantity','<','0');
   return $MonthlyReceive; 
}

// table name "order_items"
// id |product_id |quantity |price |order_id
z9gpfhce

z9gpfhce1#

$year = date("y");
for($i=1; $i<=12; $i++) {
    $MonthlyReceive = DB::table('order_items')
        ->whereBetween('created_at', array($year.'-'.$i.'-1',$year.'-'.$i.'-31'))
        ->where('quantity', '>', '0')
        ->sum(DB::raw('price*quantity'));
    return $MonthlyReceive;
}

我用这个代码把它修好了。

iyfamqjs

iyfamqjs2#

您可以每月获得聚合数据,而无需循环。

$MonthlyReceive = DB::table('order_items')
                ->select(DB::raw('sum(price*quantity) as amnt'))
                ->whereRaw('date(created_at) between "'.$year.'-01-01" and "'.$year.'-12-31"')
                ->where('quantity','<','0')
                ->groupBy(DB::raw("date_format(created_at, '%Y-%M')"));

只是使用 group by 具有聚合功能。

相关问题