主表是用户user_member表，关联的表有登录表mobile_login_log（明细）、产品统计表product_statistics（日期汇总型）、订单表order_table（明细）、转换记录表trans_log（明细）。涉及不同类型表，所以采用直接左连接查询会出现一些count/sum字段重复统计的情况，最后的查询数据结构为用户列表，字段为每个用户对应的分字段汇总数据，如用户10001登录次数、转文字次数、充值金额等。

    1. public function getWordStatisticsList($request)
    2.     {
    3.         $keyword                    = $request->input('keyword', '');
    4.         $order_situation            = $request->input('order_situation', '');
    5.         $limit                      = $request['limit'];
    6.         $page                       = $request['page'] - 1;
    7.         $order_by_column            = $request->input('field', 'user_id');
    8.         $order_by_option            = $request->input('order', 'desc');
    9.         $between                    = $this->getBetween($request);
    10.         $where['user_member.appid'] = 3;
    11.         if (isset($order_situation) && $order_situation != '') {
    12.             $where['user_member.order_situation'] = $order_situation - 1;
    13.         }
    14.         $count                  = UserMember
    15.             ::where($where)
    16.             ->when($keyword, function ($query) use ($keyword) {
    17.                 $query->where('user_member.id', 'like', "%$keyword%");
    18.             })
    19.             ->count();
    20.         $product_statistics_sql = '(
    21.         SELECT COALESCE(sum( recording_time ),0) AS recording_time,
    22.         COALESCE(sum( video_to_audio_time ),0) AS video_to_audio_time,
    23.         COALESCE(sum( audio_clips ),0) AS audio_clips,
    24.         date_time,
    25.         user_id 
    26.         FROM product_statistics';
    27.         $mobile_login_sql       = '(
    28.         SELECT count( id ) AS login_time,
    29.         user_id,status,
    30.         create_time FROM mobile_login_log where status = 1';
    31.         $order_table_sql        = '(
    32.         SELECT COALESCE ( sum( amount ), 0 ) AS amount,
    33.         user_id,
    34.         pay_status,
    35.         pay_time from order_table where pay_status = 1 and appid = 3';
    36.         $trans_log_sql          = '(
    37.         SELECT COALESCE ( sum( if(providers=1,use_time,0) ), 0 ) AS baidu_use_time,
    38.         COALESCE ( sum( if(providers=2,use_time,0) ), 0 ) AS ali_1_use_time,
    39.         COALESCE ( sum( if(providers=3,use_time,0) ), 0 ) AS ali_2_use_time,
    40.         count( id ) as trans_time ,
    41.         user_id,
    42.         date from trans_log where status = 1';
    43.         if ($between) {
    44.             $product_statistics_sql .= ' where date_time between ' . $between[0] . ' and ' . $between[1];
    45.             $mobile_login_sql       .= ' and create_time between ' . $between[0] . ' and ' . $between[1];
    46.             $order_table_sql        .= ' and pay_time between ' . $between[0] . ' and ' . $between[1];
    47.             $trans_log_sql          .= ' and date between ' . $between[0] . ' and ' . $between[1];
    48.         }
    49.         //列表数据
    50.         $list_query = UserMember::select(DB::raw('
    51.         user_member.id as user_id,
    52.         user_member.order_situation,
    53.         COALESCE(sum(bb.recording_time),0) AS recording_time,
    54.         COALESCE(sum( bb.video_to_audio_time),0) AS video_to_audio_time, 
    55.         COALESCE(sum( bb.audio_clips ),0) AS audio_clips, 
    56.         COALESCE(sum( ee.trans_time ),0) AS trans_time,
    57.         COALESCE(sum( ee.baidu_use_time ),0)/3600 + COALESCE(sum( ee.ali_1_use_time ),0)/3600 AS use_time,
    58.         COALESCE(sum( ee.baidu_use_time ),0)/3600 * 1.2 + COALESCE(sum( ee.ali_1_use_time ),0)/3600 * 1.56 AS trans_price,
    59.         COALESCE(sum( cc.login_time ),0) AS login_time,
    60.         COALESCE(sum( cc.login_time ),0)* 0.03 AS login_price,
    61.         COALESCE(sum( ee.baidu_use_time ),0)/3600 * 1.2 + COALESCE(sum( ee.ali_1_use_time ),0)/3600 * 1.56 + COALESCE(sum( cc.login_time ),0)* 0.03 as total_price,
    62.         COALESCE(sum(dd.amount),0) AS amount,
    63.         COALESCE(sum(dd.amount),0) - (COALESCE(sum( ee.baidu_use_time ),0)/3600 * 1.2 + COALESCE(sum( ee.ali_1_use_time ),0)/3600 * 1.56 + COALESCE(sum( cc.login_time ),0)* 0.03) AS profits
    64.         '))
    65.             ->LeftJoin(DB::raw($product_statistics_sql . ' GROUP BY user_id ) bb'), function ($join) {
    66.                 $join->on('user_member.id', '=', 'bb.user_id');
    67.             })
    68.             ->leftJoin(DB::raw($mobile_login_sql . ' GROUP BY user_id) cc'), function ($join) {
    69.                 $join->on('user_member.id', '=', 'cc.user_id');
    70.             })->LeftJoin(DB::raw($order_table_sql . ' group by user_id) dd'), function ($join) {
    71.                 $join->on('user_member.id', '=', 'dd.user_id');
    72.             })
    73.             ->leftJoin(DB::raw($trans_log_sql . ' group by user_id) ee'), function ($join) {
    74.                 $join->on('user_member.id', '=', 'ee.user_id');
    75.             })
    76.             ->where($where)
    77.             ->when($keyword, function ($query) use ($keyword) {
    78.                 $query->where('user_member.id', 'like', "%$keyword%");
    79.             });
    80.         if (empty($keyword)) {
    81.             $list_query->groupBy('user_member.id');
    82.         }
    83.         if ($order_by_column && $order_by_option) {
    84.             $list_query->orderBy($order_by_column, $order_by_option);
    85.         }
    86.         $list = $list_query->offset($page * $limit)->limit($limit)->get();
    87.         if (!$count) {
    88.             $list = [];
    89.         }
    90.         return [
    91.             'count' => $count,
    92.             'data'  => $list,
    93.             'msg'   => '',
    94.             'code'  => 0,
    95.         ];
    96.     }