Larave条件聚合得到多个金额总和

Question

希望一切安好。

任何人都可以告诉我如何在Laravel中以正确的方式运行此SQL查询?

控制器:

$data=DB::raw("SELECT name as 'name' FROM invoices WHERE country='$country';
SELECT SUM(amount) as 'income' FROM invoices WHERE (country='$country' AND type='income');
SELECT SUM(amount) as 'outcome' FROM invoices WHERE (country='$country' AND type='outcome')")
->groupBy('name')
->get();
return view('accounting.accounts')
->with('accounts',$data);

我希望在我的观点中这样使用它:

@foreach($accounts as $account)
<tr>
<th>{{$account->name}}</th>
<td>{{$account->income}}</td>
<td>{{$account->outcome}}</td>
</tr>
@endforeach

我是Laravel的新手，非常感谢您的帮助。提前谢谢你。

Answer 1

我相信您想要一个针对特定国家/地区内每个用户的有条件金额总和的聚合查询。在纯SQL中，可以使用CASE语句

select  name,
sum(case when type='income' then amount else 0 end) as income,
sum(case when type='outcome' then amount else 0 end) as outcome
from invoices
where country = :country
group by name
order by name

在查询生成器中可以转换为

$accounts= DB::table("invoices")
->where("country", $country)
->select([ "name",
DB::raw("sum(case when type='income' then amount else 0 end) as income"),
DB::raw("sum(case when type='outcome' then amount else 0 end) as outcome")
])
->groupBy("name")
->orderBy("name")
->get();