如何左聯接表已經分組，在我的例子中，表 A 有數據帳號，表 B 是事務，我必須在加入表 A 之前計算并按帳號分組，如果在 sql natife 中如下所示select name.account_no,amountfromci_account_name name left join ( select account_no,currency_id,sum(amount) as amount from ci_account_transaction where status <> 'X' and store_id = 62242 group by account_no,currency_id) as transon name.account_no = trans.account_no該編碼有效，但是如何在 laravel 雄辯地實現我已經嘗試了下面的代碼但是有錯誤public function reportShowZerro($data){ $return = $this->accountNameModel->select (['ci_account_name.*' ,'amount' ]) ->leftJoin(" $this->model->select(['account_no','currency_id',\DB::raw('SUM(amount) amount') ]) ->where('store_id',62242) ->where('status','<>','X') ->where('year',$data['year']) ->where('month',$data['month']) ->groupBy('account_no','currency_id') ) as trans",'ci_account_name.account_no','=','trans.account_no' ->whereIn('ci_account_name.store_id',[0,62242) ->get(); return $return; }
1 回答
縹緲止盈
TA貢獻2041條經驗 獲得超4個贊
public function reportWithModel($data){
return $this->accountNameModel->select([
'ci_account_name.*',
'trans.store_id as trans_store_id','currency_id','amount'
])->leftJoin(
\DB::raw('
(select account_no,currency_id,store_id,sum(amount) as amount from
ci_account_transaction
where status <> "X" and store_id = '.store()->id.'
and year = '.$data["year"].' and month = '.$data["month"].'
group by account_no,currency_id
) as trans
'),
'trans.account_no',
'=',
'ci_account_name.account_no'
)
->whereIn('ci_account_name.store_id',[0,store()->id])
->orderBy('ci_account_name.account_no')
->get();
}
- 1 回答
- 0 關注
- 124 瀏覽
添加回答
舉報
0/150
提交
取消