原始表如下：需要的关键字段有两个：订单编号、物料编码，下面三张表是不同统计口径，po:销售订单   ck：销售出库单    fp:销售发票。
select t1.po,t1,pn,sum(t1.qty)poqty,sum(t2.qty)ckqty,sum(t3.qty)fpqty from po t1 left join ck t2 on t1.po =t2.po and t1.pn =t2.pn left join fp t3 on t1.po =t3.po and t1.pn =t3.pn
上面这个代码计算出来的结果为:
po                pn           poqty    ckqty    fpqty
FH201403-001    A01.32.001070    920    344    320

这个结果很明显错误，正确的应该如下：
po                pn           poqty    ckqty    fpqty
FH201403-001    A01.32.001070    184    184    160

请问有木有具体解决方法，谢谢。


*下面是三张表。
po        
po                  pn            qty
FH201403-001    A01.32.001070       184
        
        
ck        
po                  pn            qty
FH201403-001    A01.32.001070       32
FH201403-001    A01.32.001070       128
FH201403-001    A01.32.001070       24
        
fp        
po                  pn            qty
FH201403-001    A01.32.001070       32
FH201403-001    A01.32.001070       128

刚才问度娘要了这个方法，请帮忙看看是不是满足了条件，会不会有其他的错误？，谢谢。

SELECT t1.date,t1.po,t1.kh,t1.ln,t1.pn,t1.qty,t2.ckqty,t3.fpqty FROM xspo t1 LEFT JOIN (sele xsck.po,xsck.ln,xsck.pn,SUM(xsck.qty)ckqty,SUM(IIF(xsck.date >=bgtime AND xsck.date<=endtime,xsck.qty,0))bqckqty FROM xsck GROUP BY 1,2,3 ) t2 ON t1.po =t2.po AND t1.ln =t2.ln AND t1.pn =t2.pn  LEFT JOIN  (sele xsfp.po,xsfp.ln,xsfp.pn,SUM(xsfp.qty)fpqty,SUM(IIF(xsfp.date >=bgtime AND xsfp.date<=endtime,xsfp.qty,0))bqfpqty FROM xsfp GROUP BY 1,2,3) t3 ON t1.po =t3.po AND t1.ln =t3.ln AND t1.pn =t3.pn

谢谢

签到加粉