连接表产生错误的金额 [英] Joining tables generating wrong sum amounts

问题描述

我之前发过这个帖子，但是它已被标记为已解决但未解决，所以我再次发帖。



我有一个查询如下：

Hi , I have posted this before, but it was marked resolved and it wasn't resolved, so I am posting again.

I have a query which is as follows:

SELECT     Prepaid.RedeemerID, OneTimePin.WithdrawerID, SUM(Prepaid.Amount) AS PrepaidAmountSum, SUM(OneTimePin.Amount) AS OneTimePinSum, SUM(Prepaid.Amount)- SUM(OneTimePin.Amount) AS 'Difference'
FROM         Prepaid, OnetimePin
WHERE   (Prepaid.RedeemerID = OneTimePin.WithdrawerID) AND (Prepaid.IsActive = 1) AND (OneTimePin.IsActive = 1) AND (Prepaid.IsRedeemed = 1) AND (OneTimePin.IsPaid = 1)
GROUP BY  Prepaid.RedeemerID,OneTimePin.WithdrawerID
ORDER BY 'Difference', Prepaid.RedeemerID, OneTimePin.WithdrawerID

但结果是不正确，它选择和疯狂的金额为onetimepin总和，也几乎加倍预付金额。



所以结果应该只显示组合



WithdrawerID | OneTimePinSum

21689 | 261200.00



AND



RedeemerID | PrepaidAmountSum

21689 | 30800.00



但它显示.....



RedeemerID | WithdrawerID | PrepaidAmountSum | OneTimePinSum |差异

21689 | 21689 | 61600.00 | 89852800.00 | -89791200.00



redeemerID和withdrawerID是一回事。



我似乎无法想象为什么会发生这种情况......



请帮助

but the results are incorrect, it selects and insane amount for the onetimepin sum and also almost double for the prepaid amount sum.

So the results should just show a combination of

WithdrawerID | OneTimePinSum
21689 | 261200.00

AND

RedeemerID | PrepaidAmountSum
21689 | 30800.00

but it shows .....

RedeemerID| WithdrawerID| PrepaidAmountSum| OneTimePinSum| Difference
21689 | 21689 | 61600.00 | 89852800.00| -89791200.00

The redeemerID and withdrawerID are the same thing.

I can't seem to figure out why this is happening...

Please help

推荐答案

请试试这个查询。 。

can you please try this query..

SELECT
    Prepaid.RedeemerID,
    OneTimePin.WithdrawerID,
    SUM(ISNULL(Prepaid.Amount,0)) AS PrepaidAmountSum,
    SUM(ISNULL(OneTimePin.Amount,0)) AS OneTimePinSum,
    SUM(ISNULL(Prepaid.Amount,0))- SUM(ISNULL(OneTimePin.Amount,0)) AS 'Difference'
FROM Prepaid
INNER JOIN OnetimePin ON Prepaid.RedeemerID = OneTimePin.WithdrawerID
WHERE  Prepaid.IsActive = 1 AND OneTimePin.IsActive = 1 AND Prepaid.IsRedeemed = 1 AND OneTimePin.IsPaid = 1
GROUP BY  Prepaid.RedeemerID,OneTimePin.WithdrawerID
ORDER BY 'Difference', Prepaid.RedeemerID, OneTimePin.WithdrawerID

这篇关于连接表产生错误的金额的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！