Select AmountLeft = SUM(AI_mPayAffirm - IP_mAmount)
From AdOrder,AdItem,InvoicePayment
Where AI_nOrderNo = AO_nOrderNo
and AI_cIfValid = 'y'
and AO_nOrderNo=2595
and AI_mPayAffirm > 0
and AI_nAdItemID = IP_nDetailID
当AI_nAdItemID = IP_nDetailID关联时,此时IP_mAmount就有值,SQL语句正常;
但当AI_nAdItemID = IP_nDetailID不关联时,查出的结果一直为0,本来应该此时IP_mAmount的值为0,直接对AI_mPayAffirm求和就可以了
请问高手,这个SQL语句应该如何修改,谢谢!
From AdOrder,AdItem,InvoicePayment
Where AI_nOrderNo = AO_nOrderNo
and AI_cIfValid = 'y'
and AO_nOrderNo=2595
and AI_mPayAffirm > 0
and AI_nAdItemID = IP_nDetailID
当AI_nAdItemID = IP_nDetailID关联时,此时IP_mAmount就有值,SQL语句正常;
但当AI_nAdItemID = IP_nDetailID不关联时,查出的结果一直为0,本来应该此时IP_mAmount的值为0,直接对AI_mPayAffirm求和就可以了
请问高手,这个SQL语句应该如何修改,谢谢!
from AdOrder A
inner join
select * from AdItem B on b.AI_nOrderNo = a.AO_nOrderNo
left Join
(select sum(IP_mAmount) IP_mAmount,IP_nDetailID from InvoicePayment group by IP_nDetailID) C on c.IP_nDetailID=a.AI_nAdItemID
where
AI_cIfValid = 'y'
and AO_nOrderNo=2595
and AI_mPayAffirm > 0不知道你的表结构,但是你可以参考