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