MYSQL左联接错误结果 [英] MYSQL LEFT JOIN INCORRECT RESULT

问题描述

我被困了很长时间，无法找到我的查询出了什么问题，LEFT JOIN中的debit column出现了问题，在下面的Query Result image中显示.我的debit table的实际值是500，但是MYSQL查询显示为1500. 我在这里做错什么了.请帮助我.

I'm stucked for a long while and unable to find whats wrong with my query, there is something wrong with my debit column in LEFT JOIN which is show in Query Result image in given below. The actual value of my debit table is 500 but MYSQL query display as 1500. What am I doing wrong here. Kindly help me please.

这是我的customers table

这是我的cust_credit table

这是我的cust_debit table

MYSQL Query如下

SELECT 
    customers.id as id, 
    customers.cust_name AS customer_name,
    SUM(cust_debit.debit_amount) as debit,
    SUM(cust_credit.credit_amount) as credit,
    (SUM(cust_debit.debit_amount)) - (SUM(cust_credit.credit_amount)) as balance


    FROM customers
  LEFT JOIN  cust_debit  ON customers.id = cust_debit.cust_id
  LEFT JOIN  cust_credit ON customers.id = cust_credit.cust_id


  GROUP BY customers.id
  ORDER BY customers.id

我的Query Result如下

推荐答案

信用表中有多行，这使得引起问题的组之前有多行.如果您删除分组依据并选择所有列，则可以看到此信息.

You have multiple rows in the credit table, this makes multiple rows before the group by which causes the problem. You can see this if you take out the group by and select all columns.

如果在子联接中分组依据消失了，那么在分组依据之前每个客户只有一行.

If you group by in a sub join this problem goes away then you have one row per customer before the group by.

SELECT 
  customers.id as id, 
  customers.cust_name AS customer_name,
  SUM(cust_debit.debit_amount) as debit,
  SUM(cust_credit.credit_amount) as credit,
  (SUM(cust_debit.debit_amount)) - (SUM(cust_credit.credit_amount)) as balance
FROM customers
LEFT JOIN cust_debit  ON customers.id = cust_debit.cust_id
LEFT JOIN (
  SELECT cust_id, sum(credit_amount) as credit_amount)
  from cust_credit
  group by cust_id
) cust_credit ON customers.id = cust_credit.cust_id
GROUP BY customers.id
ORDER BY customers.id

这篇关于MYSQL左联接错误结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！