mysql - 加入冗余总和

我的查询是（在此查询中，我们得到冗余结果错误值得到）

SELECT
ggk_user.user_id,
ggk_user.user_email,
SUM(ggk_shop_item.item_price) as total_amount,
SUM(ggk_userpayment.up_amount) as paid
FROM ggk_user

LEFT JOIN ggk_product ON ggk_user.user_id = ggk_product.product_user_id
LEFT JOIN ggk_shop_item ON ggk_product.product_id = ggk_shop_item.item_product_id
INNER JOIN ggk_userpayment ON ggk_user.user_id = ggk_userpayment.up_user_id


WHERE ggk_user.user_type = 1
AND ggk_user.user_status = 1


GROUP BY ggk_user.user_id,ggk_userpayment.up_user_id

如果运行此查询。

SELECT
ggk_user.user_id,
ggk_user.user_email,
SUM(ggk_shop_item.item_price) as total_amount
FROM ggk_user

LEFT JOIN ggk_product ON ggk_user.user_id = ggk_product.product_user_id
LEFT JOIN ggk_shop_item ON ggk_product.product_id = ggk_shop_item.item_product_id


WHERE ggk_user.user_type = 1
AND ggk_user.user_status = 1


GROUP BY ggk_user.user_id,ggk_userpayment.up_user_id

此查询工作正常，因此由于第3个连接的用户ID等于用户表用户ID，导致第3个连接出现问题，因此如何解决此错误？

最佳答案

使用内联视图从GGK_USERpayment生成已付款额，因此表之间的基数不会影响其他金额。

类似于下面的内容；但我们可能还需要为total_amount生成一个内联视图。我对表之间的关系不太了解，无法确定是否需要。

SELECT ggk_user.user_id
     , ggk_user.user_email
     , SUM(ggk_shop_item.item_price) as total_amount
     , ggk_userpayment.paid
FROM ggk_user
INNER JOIN (SELECT sum(up_Amount) as Paid, up_user_ID
            FROM ggk_userpayment
            GROUP BY up_user_ID ) ggk_userpayment
   ON ggk_user.user_id = ggk_userpayment.up_user_id
LEFT JOIN ggk_product
   ON ggk_user.user_id = ggk_product.product_user_id
LEFT JOIN ggk_shop_item
   ON ggk_product.product_id = ggk_shop_item.item_product_id
WHERE ggk_user.user_type = 1
  AND ggk_user.user_status = 1
GROUP BY ggk_user.user_id
       , ggk_userpayment.up_user_id

关于mysql - 加入冗余总和，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/42206484/