select
a.ClientID,
f.Currency,
a.OrganizationName,
COALESCE(sum(b.GrandTotal),0) as SaleGrandTotal,
COALESCE(sum(g.AmountReceived),0) as AmountReceived,
COALESCE(sum(b.GrandTotal - g.AmountReceived),0) as SaleBalanceRemaining,
COALESCE(sum(d.GrandTotal), 0) as PurchaseGrandTotal,
COALESCE(sum(e.AmountPaid), 0) as AmountPaid,
COALESCE(sum(d.GrandTotal - e.AmountPaid),0) as PurchaseBalanceRemaining,
COALESCE(sum(b.GrandTotal - g.AmountReceived),0) - COALESCE(sum(d.GrandTotal - e.AmountPaid),0) as Total
from na_clients as a
join na_currency as f
left join na_transaction as b
on a.ClientID = b.ClientID and b.CurrencyID = f.CurrencyID and b.IsActive = 1
left join na_recoverylogs as g
on b.TID = g.TID
left join na_purchase as d
on a.ClientID = d.ClientID and d.CurrencyID = f.CurrencyID and d.IsActive = 1
left join na_purchaselogs as e
on e.PID = d.PID
group by a.OrganizationName,f.Currency
order by a.OrganizationName
我正在使用美元，人民币，卢比等多种货币。它工作正常，但今天我注意到sum（）double值如b.GrandTotal应该为11500，但其返回值为23000
Table Client:
clientid,name,organizationName
1,client1,OrgName
2,client2,OrgName
Table Currency:
currencyid,cname
1,Dollar
2,Rupees
Table Transaction:
tid,clientid,currencyid,grandTotal,amountReceived,balanceremaining
1,1,1,11000,0,11000
2,1,1,500,0,500
Table recoveryLogs: // Another Error Here
id,tid,amountreceived
1,1,0
2,2,0
3,2,2000 // Again sum() multiply value - because of PID 2 is repeating
Table Purchase:
pid,clientid,currencyid,grandTotal,amountPaid,balanceRemaining
1,1,1,25000,0,25000
1,2,2,2,3000,1000,2000
Now I am using sum(b.grandTotal) instead of 11500 it return 23000
Table PurchaseLogs: // Another Error Here
id,pid,amountpaid
1,1,0
2,2,1000
3,1,1000 // Again sum() multiply value - because of PID 1 is repeating
所以结果应该是：
Client: Client1
SaleGrandTotal: 11500
AmountReceived: 0
SaleBalanceRemaining: 11500
PurchaseGrandTotal: 25000
AmountPaid: 0
PurchaseBalanceRemaining: 25000
Total Amount: -13500
但结果我得到：
Client: Client1
SaleGrandTotal: 23000
AmountReceived: 0
SaleBalanceRemaining: 23000
PurchaseGrandTotal: 50000
AmountPaid: 0
PurchaseBalanceRemaining: 50000
Total Amount: -27000
如果我从查询中删除购买条款（d和e）或transaction（b和g）条款，则它可以单独正常工作。
数据加倍的原因是您ClientID在“交易”和“购买”表中出现的次数不同，因此不是一对一匹配。ClientID = 1并且CurrencyID = 1在“交易”中出现两次，在“购买”中只出现一次。当您联接表时，结果组合为1 x 2 = 2ClientID记录，其中某些字段重复数据。因此，对于重复输入，求和将加倍。如图所示：
Transaction Data | Purchase Data
row1: 1,1,1,11000,0,11000 | 1,1,1,25000,0,25000
row2: 2,1,1,500,0,500 | 1,1,1,25000,0,25000
考虑使用派生表将两个表之间的聚合分开。然后，将四个基础聚合（交易，购买，恢复日志，购买日志）连接起来以进行最终查询。在加盟将匹配1对1，如果您汇总，在分组ClientID和CurrencyID，TID和PID。
SELECT
transAgg.ClientID, transAgg.Currency, transAgg.OrganizationName,
transAgg.SaleGrandTotal, recovLogAgg.SumOfAmtReceived,
(transAgg.SaleGrandTotal - recovLogAgg.SumOfAmtReceived) as SaleBalanceRemaining,
purchAgg.PurchaseGrandTotal, purchLogAgg.SumOfAmtPaid,
(purchAgg.PurchaseGrandTotal - purchLogAgg.SumOfAmtPaid) as PurchaseBalanceRemaining,
((transAgg.SaleGrandTotal - recovLogAgg.SumOfAmtReceived) -
(purchAgg.PurchaseGrandTotal - purchLogAgg.SumOfAmtPaid)) As [Total]
FROM
(SELECT
a.ClientID, f.CurrencyID, f.Currency, a.OrganizationName,
COALESCE(sum(b.GrandTotal),0) as SaleGrandTotal
FROM na_clients as a
INNER JOIN na_currency as f
LEFT JOIN na_transaction as b
ON a.ClientID = b.ClientID
AND b.CurrencyID = f.CurrencyID
AND b.IsActive = 1
GROUP BY a.ClientID, a.OrganizationName, f.CurrencyID, f.Currency
ORDER BY a.OrganizationName) As transAgg
INNER JOIN
(SELECT
a.ClientID, f.CurrencyID, f.Currency, a.OrganizationName,
COALESCE(sum(d.GrandTotal), 0) as PurchaseGrandTotal
FROM na_clients as a
INNER JOIN na_currency as f
LEFT JOIN na_purchase as d
ON a.ClientID = d.ClientID
AND d.CurrencyID = f.CurrencyID
AND d.IsActive = 1
GROUP BY a.ClientID, a.OrganizationName, f.CurrencyID, f.Currency
ORDER BY a.OrganizationName) As purchAgg
ON transAgg.ClientID = purchAgg.ClientID
AND transAgg.CurrencyID = purchAgg.CurrencyID
INNER JOIN
(SELECT
g.TID, COALESCE(sum(g.AmountReceived),0) As SumOfAmtReceived
FROM na_recoverylogs as g
GROUP BY g.TID) As recovlogAgg
ON transAgg.TID = recovlogAgg.TID
INNER JOIN
(SELECT
e.PID, COALESCE(sum(e.AmountPaid),0) As SumOfAmtPaid
FROM na_purchaselogs as e
GROUP BY e.PID) As purchlogAgg
ON purchAgg.PID = purchlogAgg.PID
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句