SELECT Gi.Billnum as oldbillnum,Gi.Gysnum,T_gys.gysname ,Gi.Billdate as oldbilldate,'入库单' as oldbilltype ,iif(isnull(Gi.TotSum),0,Gi.TotSum) as oldTotSum,iif(isnull(Gi.yhje),0,Gi.yhje) as yhpay,(IIf(IsNull(Sum(ps2.Thispay)),0,Sum(ps2.Thispay))+IIf(IsNull(Sum(hx.Thispay)),0,Sum(hx.Thispay))) AS Yespay,Gi.zy as rem,Gi.selects,
iif(isnull(Gi.TotSum),0,Gi.TotSum)-iif(isnull(Gi.yhje),0,Gi.yhje)-(IIf(IsNull(Sum(ps2.Thispay)),0,Sum(ps2.Thispay))+IIf(IsNull(Sum(hx.Thispay)),0,Sum(hx.Thispay))) as Nopay
FROM (((Select Billnum,Gysnum,billdate,Totsum,yhje,paysum,zy,selects from B_GoodsIn ) as Gi
LEFT outer JOIN B_paylist ps2 ON Gi.Billnum=ps2.oldBillnum)Left JOIN B_othhxlist as hx ON Gi.Billnum = hx.oldbillnum)
left outer join T_gys on Gi.Gysnum=T_gys.Gysnum
Group by Gi.billnum,Gi.gysnum,T_gys.gysname,Gi.Billdate,Gi.totsum,Gi.yhje,Gi.zy,Gi.selects;B_goodsin as Gi 是单据表,B_paylist as ps2 是付款表,B_othhxlist as hx 是其他付款表
每个表中都有Billnum字段对应,我想查询出Gi表中的某个单据的已付款的合计值,也就是ps2中对应单据的合计值+hx中对应单据的合计值;
使用上面的语句后出现了这样的错误:ps2表中有某单据的3次付款记录,而hx表中有该单据的1次付款记录,合计出来的值中hx表中的1条记录的合计值被加了3次;
同样如果Ps2表中有5条记录,hx表中有2条记录,那么合计出来的值中hx的2条记录的合计值被加了5次;
应该是语句错误,请大家指点下!!
iif(isnull(Gi.TotSum),0,Gi.TotSum)-iif(isnull(Gi.yhje),0,Gi.yhje)-(IIf(IsNull(Sum(ps2.Thispay)),0,Sum(ps2.Thispay))+IIf(IsNull(Sum(hx.Thispay)),0,Sum(hx.Thispay))) as Nopay
FROM (((Select Billnum,Gysnum,billdate,Totsum,yhje,paysum,zy,selects from B_GoodsIn ) as Gi
LEFT outer JOIN B_paylist ps2 ON Gi.Billnum=ps2.oldBillnum)Left JOIN B_othhxlist as hx ON Gi.Billnum = hx.oldbillnum)
left outer join T_gys on Gi.Gysnum=T_gys.Gysnum
Group by Gi.billnum,Gi.gysnum,T_gys.gysname,Gi.Billdate,Gi.totsum,Gi.yhje,Gi.zy,Gi.selects;B_goodsin as Gi 是单据表,B_paylist as ps2 是付款表,B_othhxlist as hx 是其他付款表
每个表中都有Billnum字段对应,我想查询出Gi表中的某个单据的已付款的合计值,也就是ps2中对应单据的合计值+hx中对应单据的合计值;
使用上面的语句后出现了这样的错误:ps2表中有某单据的3次付款记录,而hx表中有该单据的1次付款记录,合计出来的值中hx表中的1条记录的合计值被加了3次;
同样如果Ps2表中有5条记录,hx表中有2条记录,那么合计出来的值中hx的2条记录的合计值被加了5次;
应该是语句错误,请大家指点下!!
SELECT Gi.Billnum as oldbillnum,
Gi.Gysnum,T_gys.gysname ,Gi.Billdate as oldbilldate,
'入库单' as oldbilltype ,
iif(isnull(Gi.TotSum),0,Gi.TotSum) as oldTotSum,
iif(isnull(Gi.yhje),0,Gi.yhje) as yhpay,
(IIf(IsNull(Sum(ps2.Thispay)),0,Sum(ps2.Thispay))+IIf(IsNull(Sum(hx.Thispay)),0,Sum(hx.Thispay))) AS Yespay,
Gi.zy as rem,Gi.selects,
iif(isnull(Gi.TotSum),0,Gi.TotSum)-iif(isnull(Gi.yhje),0,Gi.yhje)-(IIf(IsNull(Sum(ps2.Thispay)),
0,
Sum(ps2.Thispay))+IIf(IsNull(Sum(hx.Thispay)),0,
Sum(hx.Thispay))) as Nopay
FROM
((
(Select Billnum,Gysnum,billdate,Totsum,yhje,paysum,zy,selects
from B_GoodsIn ) as Gi
right outer JOIN B_paylist ps2 ON Gi.Billnum=ps2.oldBillnum)
right JOIN B_othhxlist as hx ON Gi.Billnum = hx.oldbillnum)
right outer join T_gys on Gi.Gysnum=T_gys.Gysnum
Group by Gi.billnum,Gi.gysnum,T_gys.gysname,Gi.Billdate,Gi.totsum,Gi.yhje,Gi.zy,Gi.selects;不知道 T_gys 是什么信息,最后一个连接可能不对我觉得应该是 对ps2,hx先分组合计出每个单据的总付款数再连接Gi,得到此单据的其他信息
这样看起来清晰,操作起来也简单得多
那么最好先用union all 三个表 这个语句可存为视图
然后再根据这一视图再 inner join 三个表,这样即可以出现三个表中名字,又能统计三个表中的数据,且不会相加多次。
你可以改为inner join试试