Mysql leftjoin一对多关系导致sum数据翻倍 解决方案

Mysql leftjoin一对多关系导致sum数据翻倍 解决方案

案例

数据表 用户表(user)，订单表(order)，充值表(recharge)

关联关系 user.id = order.uid = recharge.uid

使用 leftjoin 的时候会导致 t_01 的数据出现多次，再对 t_01 使用 sum 时，会有 sum 数据翻倍的情况

错误案例：查询用户充值总额和消费

SELECT user.id,user.name,
	COALESCE (SUM(r.amount), 2) AS total_amount,
	COALESCE (SUM(o.amount), 2) AS total_order_amount
from user 
LEFT JOIN order as o on user.id = o.uid 
LEFT JOIN recharge as r on user.id = r.uid 
GROUP BY users.id

解决方案：

–避免直接使用两个表进行 left join

–先处理 一对多 中的 ‘多表’ ，现将多表中的数据聚合为一条

–再将处理过的表与 一对多 中的 ‘一表’ 进行关联 sql如下：

SELECT user.id,user.name,r.total_amount,o.total_order_amount
from user 
LEFT JOIN (select uid,COALESCE (SUM(r.amount), 2) AS total_amount from echarge as r group by uid) r
on user.id = r.uid
LEFT JOIN (select uid,COALESCE (SUM(o.amount), 2) AS total_order_amount from order as o group by uid) o
on user.id = o.uid
GROUP BY users.id

成功解决！！！

PS：以下是在Laravel框架中的写法

$query = DB::table('user')
         ->leftJoin(DB::raw('(SELECT
                        uid,COALESCE (SUM(r.amount), 2) AS total_recharge
                        FROM recharge as r
                    GROUP BY uid) as r'), 'user.id', '=', 'r.uid')
        ->leftJoin(DB::raw('(SELECT
                        uid,COALESCE (SUM(o.amount), 2) AS total_order_amount 
                        FROM order as o
                    GROUP BY merchant_id) as o'), 'user.id', '=', 'o.uid')
            ->select('user.id', 'r.total_recharge', 'o.total_order_amount')
            ->groupBy('user.id')
            ->orderByDesc('r.total_recharge');