问题描述
我之前发过这个帖子,但是它已被标记为已解决但未解决,所以我再次发帖。
我有一个查询如下:
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
推荐答案
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
这篇关于连接表产生错误的金额的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!