mysql - 在加入冗余和得到

Question

我的查询是(在这个查询中我们得到了冗余结果错误的值)

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

此查询工作正常，因此由于第三个联接用户 ID 等于用户表用户 ID，因此在第三个联接中出现问题，那么如何修复此错误？

Answer 1

使用内联 View 从 GGK_USER payment 生成支付金额，以便表之间的基数不会影响其他金额。

类似于下面的内容；但我们可能还需要为total_amount生成一个内联 View 。我不太了解表之间的关系，不知道是否需要它。

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