laravel mysql exists_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_total

from (

select

t.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 = 1

and 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 基本是一样的。