laravel 中MySQL临时表使用

MySQL原生语句

顾客分析-消费次数分析

select  sum(if(tmp.pay_number =1,1,0)) as frequency_1,  sum(if(tmp.pay_number = 1,tmp.pay_number,0)) as frequency_1_number,  sum(if(tmp.pay_number >=2 and tmp.pay_number <=8,1,0)) as frequency_2,  sum(if(tmp.pay_number >=2 and tmp.pay_number <=8,tmp.pay_number,0)) as frequency_2_number,  sum(if(tmp.pay_number >=9 and tmp.pay_number <=15,1,0)) as frequency_3,  sum(if(tmp.pay_number >=9 and tmp.pay_number <=15,tmp.pay_number,0)) as frequency_3_number,  sum(if(tmp.pay_number >15,1,0)) as frequency_4,  sum(if(tmp.pay_number >15,tmp.pay_number,0)) as frequency_4_number,  count(*) as frequency_total,  sum(tmp.pay_number) as frequency_number_totalfrom (  selectt.membership_id,count(*) as pay_number,sum(t.receivable) sum_receipts  from transactions t  where t.end_time >= '2018-05-01'and t.end_time <= '2018-05-31 23:59:59'and t.membership_id > 0// and t.pay_way = 1and t.business_id = 9  group by t.membership_id) tmp;

laravel 中的代码

 //根据会员ID分组，得出一个月此商户会员数，各个会员支付的笔数，以及每个会员的总金额$tmp = $transaction->selectRaw('membership_id,count(*) as pay_number,sum(receivable) as sale_amount')->whereBetween('end_time',[$start_time,$end_time])->where('membership_id','>',0)->where('business_id','=',$businessId) //当前商户->where('status','=',1)//交易状态为：成功->groupBy('membership_id'); //根据用户会员分组//根据上面的数据再获得相应次数下的各个用户数，支付笔数，金额数$v = \DB::table(\DB::raw("({$tmp->toSql()}) as t"))->mergeBindings($tmp->getQuery())->selectRaw('sum(if(t.pay_number =1,1,0)) as frequency_1_custombers,sum(if(t.pay_number = 1,t.pay_number,0)) as frequency_1_pay_number,sum(if(t.pay_number = 1,t.sale_amount,0)) as frequency_1_sale_amount,sum(if(t.pay_number >=2 and t.pay_number <=8,1,0)) as frequency_2_8_custombers,sum(if(t.pay_number >=2 and t.pay_number <=8,t.pay_number,0)) as frequency_2_8_pay_number,sum(if(t.pay_number >=2 and t.pay_number <=8,t.sale_amount,0)) as frequency_2_8_sale_amount,sum(if(t.pay_number >=9 and t.pay_number <=15,1,0)) as frequency_9_15_custombers,sum(if(t.pay_number >=9 and t.pay_number <=15,t.pay_number,0)) as frequency_9_15_pay_number,sum(if(t.pay_number >=9 and t.pay_number <=15,t.sale_amount,0)) as frequency_9_15_sale_amount,sum(if(t.pay_number >15,1,0)) as frequency_15_custombers,sum(if(t.pay_number >15,t.pay_number,0)) as frequency_15_pay_number,sum(if(t.pay_number >15,t.sale_amount,0)) as frequency_15_sale_amount,count(*) as total_custombers,sum(t.pay_number) as total_pay_number,sum(t.sale_amount) as total_sale_amount')->first();

// 合并绑定参数$query->mergeBindings($subQuery);OR$query->mergeBindings($subQuery->getQuery());\DB::table(\DB::raw('('.$subQuery.') as tt'))注意合并参数时 $subQuery 必须是 \Illuminate\Database\Query\Builder 类型如果是 \Illuminate\Database\Eloquent\Builder 类型的，用 getQuery() 方法不用 DB::raw() 直接写子查询，是因为查询带比较多的 where 条件和 group by, 而且内层查询和外层查询的 where 基本是一样的。