原始表如下：需要的关键字段有两个：订单编号、物料编码，下面三张表是不同统计口径，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

典型的分组求和
有两种分组方法
1 po,pn分组
2 pn,po分组
这两种分组方法，结果可能不一样

[ 本帖最后由 sdta 于 2014-5-25 16:55 编辑 ]

Select A.Po,A.Pn,PoQty,CkQty,FpQty from ;
(Select Po,Pn,Sum(Qty) PoQty From Po Group By 1,2) A,;
(Select Po,Pn,Sum(Qty) CkQty From Ck Group By 1,2) B,;
(Select Po,Pn,Sum(Qty) FpQty From Fp Group By 1,2) C ;
Where A.Po==B.Po And B.Po==C.Po

SELECT a.po,a.pn,a.qty poqty,b.qty ckqty,c.qty fpqty FROM (sele po,pn,SUM(qty) qty FROM po GROUP BY 1,2) a ;
LEFT JOIN (sele po,pn,SUM(qty) qty FROM ck GROUP BY 1,2) b ON a.po=b.po ;
LEFT JOIN (sele po,pn,SUM(qty) qty FROM fp GROUP BY 1,2) c ON a.po=c.po

谢谢

签到加粉