mysql - 用另一列的列和组的总和连接两个表

Question

需要帮助使用 SUM() 函数连接和分组两个表。
表格如下:

DEBIT TABLE                 CREDIT TABLE                
---------------------       ---------------------
CustomerName    Debit       CustomerName    Credit
---------------------       ---------------------
Customer1       200.00      Customer1       100.00
Customer1       300.00      Customer1       100.00
Customer2       100.00      Customer1       100.00
Customer2       600.00      Customer2       200.00
-----------------------     Customer2       300.00
                            Customer2       50.00
                            -----------------------

我需要将两个 Join 表显示为:

JOIN TABLE(DEBIT CREDIT TABLE)              
------------------------------------------
CustomerName    Debit   Credit  Closing
------------------------------------------
Customer1       500.00  300.00  200.00
Customer2       700.00  550.00  150.00
------------------------------------------

这是我得到的，但没有产生正确的结果:

"SELECT Debit_Tbl.CustomerName, SUM(Debit_Tbl.Debit) as Debit, SUM(Credit_Tbl.Credit) as Credit, SUM(Debit_Tbl.Debit) - SUM(Credit_Tbl.Credit) as Closing from Debit_Tbl LEFT OUTER JOIN Credit_Tbl ON Debit_Tbl.CustomerName = Credit_Tbl.CustomerName GROUP BY Debit_Tbl.debit UNION SELECT Debit_Tbl.CustomerName, SUM(Debit_Tbl.Debit) as Debit, SUM(Credit_Tbl.Credit) as Credit, SUM(Debit_Tbl.Debit) - SUM(Credit_Tbl.Credit) as Closing from Debit RIGHT OUTER JOIN Credit_Tbl ON Debit_Tbl.CustomerName = Credit_Tbl.CustomerName GROUP BY Debit_Tbl.debit"
-------------------------------------------

Answer 1

您的查询正在生成两个表之间的笛卡尔积。相反，使用 union all 将借方和贷方分开到单独的列中——而不乘以行数。然后进行聚合:

select customerName, sum(debit) as debit, sum(credit) as credit,
       sum(debit) - sum(credit) as closing
from (select d.customerName, debit as debit, 0 as credit
      from debit d
      union all
      select c.customerName, 0 as debit, credit
      from credit c
     ) cd
group by customerName;