Laravel如何用sql求百分比?

假如有个order 表 里有两个字段

goods_id
pay_price

1 以goods_id分组查询

select sum(pay_price) as one_goods_total_price from order group by goods_id

2 查出所有商品总价格

select sum(pay_price) as goods__total_price from order 

3 合并到一条查询

select one_goods__total_price,goods_total_price,CONCAT(ROUND(one_goods__total_price / goods_total_price * 100, 2),'','%') as per from (select sum(pay_price) as one_goods__total_price from order group by goods_id) as one_goods,(select sum(pay_price) as goods_total_price from order) as goods_total

怎么用Laravel 实现上方的查询呢

这样

$subject=DB::table('order')->select(DB::raw("sum(pay_price) as one_goods_total_price")->groupBy('goods_id');

$subject1=DB::table('order')->select(DB::raw("sum(pay_price) as one_goods_total_price");
$subject2=DB::query()->select(DB::raw("CONCAT(ROUND(one_goods__total_price / goods_total_price * 100, 2),'','%') as per")->fromRaw("(" .$ubject->toSql().") as one_goods".",(" .$subject1->toSql().") as goods_total");

//假如$subject和$subject1有其他判断条件,要这样

$subject2->mergeBindings($subSelect)->mergeBindings($subSelect2);
//要不where的判断条件会出错

Posted in laravel, 后端 on Oct 29, 2019

请登录 登录 评论!