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