laravel 多个 multi join 多个 multi sum 两个 two join 两个 two sum mysql join sum时数据重复问题及解决方案 sum 数值变几倍 有大用 有大大用 有大大大用 有大大大大用

mysql 原理 见 /node-admin/16147

通过这 3 个表，我想像这样恢复salary.id 和salary.name 和 sum (pointages.sold) 和 sum (avances.montantA)
Salarie.id  Salaries.name Sum(sold)   Sum(montantA)
1              kamal           220          170
2              imad            70           0
当我执行 mysql 请求时，它给了我一个确切的结果：
SELECT 
     s.id AS 'Salarie.id', 
     s.nom AS 'Salarie.nom', 
     COALESCE(p.somme, 0) AS 'SUM(sold)', 
     COALESCE(a.somme, 0) AS 'SUM(montantA)' 
FROM   Salaries s 
LEFT JOIN (SELECT SUM(sold) AS somme, salarie_id FROM pointages 
           GROUP BY salarie_id) AS p ON p.salarie_id = s.id
 LEFT JOIN (SELECT SUM(montantA) AS somme, salarie_id FROM avances 
           GROUP BY salarie_id) AS a ON a.salarie_id = s.id
GROUP BY s.id, p.salarie_id, a.salarie_id

现在我想在 SalarieController 的状态函数中编写此查询，但我不知道如何

如果像下面这样使用 sum结果就是几位的值,就不对了
SELECT 
s.id AS 'Salarie.id', 
s.nom AS 'Salarie.nom', 
COALESCE(p.somme, 0) AS 'SUM(sold)', 
 COALESCE(a.somme, 0) AS 'SUM(montantA)' 
FROM 
Salaries s 
 LEFT JOIN pointages AS p ON p.salarie_id = s.id
 LEFT JOIN avances ON a.salarie_id = s.id
GROUP BY s.id, p.salarie_id, a.salarie_id

$p = DB::table('pointages')
        ->groupBy('salarie_id')
        ->selectRaw('SUM(sold) AS somme, salarie_id');

$a = DB::table('avances')
        ->groupBy('salarie_id')
        ->selectRaw('SUM(montantA) AS somme, salarie_id');

DB::table('Salaries AS s')
    ->leftJoin(DB::raw("({$p->toSql()}) AS p"), 'p.salarie_id', '=', 's.id')
    ->leftJoin(DB::raw("({$a->toSql()}) AS a"), 'a.salarie_id', '=', 's.id')
    ->groupBy('s.id', 'p.salarie_id', 'a.salarie_id')
    ->selectRaw('s.id AS "Salarie.id", 
              s.nom AS "Salarie.nom", 
              COALESCE(p.somme, 0) AS SUM(sold), 
              COALESCE(a.somme, 0) AS SUM(montantA)')
    ->get();

DB::table('Salaries AS s')
   ->leftJoin('pointages AS p', 'p.salarie_id', '=', 's.id')
   ->leftJoin('avances AS a', 'a.salarie_id', '=', 's.id')
   ->groupBy('s.id')
   ->selectRaw('s.id AS "Salarie.id",
                s.name AS "Salaries.nom",
                SUM(p.sold),
                SUM(a.montantA)')
   ->get();

来自  https://stackoverflow.com/questions/60660069/sum-left-join-multiple-tables-laravel6

下面是我自己亲自做的 有大用

//下面这里 从个 join 多个 SUM 结果会不正确 
//        $customer =
//            Customer::select('customers.customer_id as customer_id','city_name as city_name_join','store_name as store_name_join',
//                'customers.city_id as city_id','customer_name',\DB::raw('SUM(operation_num) as xiao_fei_num_join'),
//                \DB::raw('SUM(shoukuan_amount) AS xiao_fei_amounts_join'),\DB::raw('SUM(trade_yiji) as trade_yiji_join'),
//                \DB::raw('count(trades.trade_id) AS chika_num'),\DB::raw('MAX(operation_date) as last_consume_date'),
//                'customer_ziliao_imgs','customer_duibitu_imgs')
//                ->joinCity()->joinStore()->leftJoinOperation()->leftJoinShoukuan()->leftJoinTrade()->groupby("customers.customer_id");
        
        下面才是正确的 主表Cusomter,有四个副表 operations(模型是Operation) shoukuans模型是Shoukuan) trades模型是Trade),\
        三个副表的外键id 都是 customer_id

            $oper_db = Operation::select(\DB::raw('SUM(operation_num) as xiao_fei_num_join'),\DB::raw('MAX(operation_date) as last_consume_date'),'customer_id')->groupBy('customer_id');
        $shou_db = Shoukuan::select(\DB::raw('SUM(shoukuan_amount) AS xiao_fei_amounts_join'),'customer_id')->groupBy('customer_id');
        $tra_db = Trade::select(\DB::raw('SUM(trade_yiji) as trade_yiji_join'),\DB::raw('count(trades.trade_id) AS chika_num'),'customer_id')->groupBy('customer_id');

        $customer = Customer::select('customers.customer_id as customer_id','city_name as city_name_join','store_name as store_name_join',
                'customers.city_id as city_id','customer_name',
               'oper_db.xiao_fei_num_join', 'shou_db.xiao_fei_amounts_join', 'tra_db.trade_yiji_join', 'tra_db.chika_num', 'oper_db.last_consume_date',
              'customer_ziliao_imgs','customer_duibitu_imgs')
            ->leftJoin(\DB::raw("({$oper_db->toSql()}) AS oper_db"), 'oper_db.customer_id', '=', 'customers.customer_id')
            ->leftJoin(\DB::raw("({$shou_db->toSql()}) AS shou_db"), 'shou_db.customer_id', '=', 'customers.customer_id')
            ->leftJoin(\DB::raw("({$tra_db->toSql()}) AS tra_db"), 'tra_db.customer_id', '=', 'customers.customer_id')
            ->joinCity()->joinStore()->groupby("customers.customer_id");