sql汇总sum为0

Question

  SELECT
                           A.ORDSEQ, A.ORDNO, A.ORDDT,
                           C.SHPNO, C.SHPDT,
                           E.ORDSEQ PCORDSEQ, E.ORDNO PCORDNO,
                           SUM(D.PRC*C.EXRT*D.QTY)-SUM(G.PRC*F.EXRT*G.QTY) GPAMT,
                           M0.CODE, NVL(M0.CNAME,M0.LNAME) CLIENTNAME,
                           M1.CODE ORIGINCODE, M1.LNAME ORIGINNAME,
                           M2.CODE DESTCODE, M2.LNAME DEST,
                           SUM(D.QTY) QTY,
                           SUM(D.PRC*C.EXRT*D.QTY)  AMT,
                           (SUM(D.PRC*C.EXRT*D.QTY)-SUM(G.PRC*F.EXRT*G.QTY))/DECODE(SUM(D.PRC*C.EXRT*D.QTY),0,1,SUM(D.PRC*C.EXRT*D.QTY))*100 GPPER
  FROM               ORD A, ORDITM B, ORDSHP C, ORDITMDTL D, ORD E, ORDSHP F, ORDITMDTL G,
                           CTRPTY M0, CTRY M1, CTRY M2
  WHERE           A.ORDTY='S'
  AND                 A.ORDSEQ=B.ORDSEQ
  AND                 B.ORDSEQ=C.ORDSEQ
  AND                 C.ORDSEQ=D.ORDSEQ
  AND                 C.SHPNO=D.SHPNO
  AND                 D.ITMNO=B.ITMNO
  AND                 E.ORDTY IN ('C')
  AND                 E.RELORD(+)=A.ORDSEQ
  AND                 F.ORDSEQ=E.ORDSEQ
  AND                 G.ORDSEQ=E.ORDSEQ
  AND                 G.ITMNO=D.ITMNO
  AND                 G.SHPNO=D.SHPNO
  AND                 G.SHPNO=F.SHPNO
  AND                 G.CLNO=D.CLNO
  AND                 G.SZNO=D.SZNO
  AND                 A.CTRPTY=M0.CODE(+)
  AND                 A.ORIGIN=M1.CODE(+)
  AND                 C.DEST=M2.CODE(+)
  AND                 E.ORDSEQ IS NOT NULL
  AND                 A.ORDNO='SC10-0457'
  GROUP BY
                          A.ORDSEQ, A.ORDNO, A.ORDDT,
                          C.SHPNO, C.SHPDT,
                          D.ITMNO,
                          E.ORDSEQ, E.ORDNO,
                          M0.CODE, M0.LNAME,  M0.CNAME,
                          M1.CODE, M1.LNAME,
                          M2.CODE, M2.LNAME
表D是存的订单的款式的详细表
 当其中有个数量为0的时候， 所有的 汇总就都为0了
 请教下是什么原因造成的 

Answer 1

---- SQL Server的话加个isnull试试
-- oracle的话，用nvl试试
SELECT
                           A.ORDSEQ, A.ORDNO, A.ORDDT,
                           C.SHPNO, C.SHPDT,
                           E.ORDSEQ PCORDSEQ, E.ORDNO PCORDNO,
                           SUM(isnull(D.PRC*C.EXRT*D.QTY,0))-SUM(isnull(G.PRC*F.EXRT*G.QTY,0)) GPAMT,
                           M0.CODE, NVL(M0.CNAME,M0.LNAME) CLIENTNAME,
                           M1.CODE ORIGINCODE, M1.LNAME ORIGINNAME,
                           M2.CODE DESTCODE, M2.LNAME DEST,
                           SUM(isnull(D.QTY,0)) QTY,
                           SUM(isnull(D.PRC*C.EXRT*D.QTY,0))  AMT,
                           (SUM(isnull(D.PRC*C.EXRT*D.QTY,0))-SUM(isnull(G.PRC*F.EXRT*G.QTY,0)))/DECODE(SUM(isnull(D.PRC*C.EXRT*D.QTY,0)),0,1,SUM(isnull(D.PRC*C.EXRT*D.QTY,0)))*100 GPPER
  FROM               ORD A, ORDITM B, ORDSHP C, ORDITMDTL D, ORD E, ORDSHP F, ORDITMDTL G,
                           CTRPTY M0, CTRY M1, CTRY M2
  WHERE           A.ORDTY='S'
  AND                 A.ORDSEQ=B.ORDSEQ
  AND                 B.ORDSEQ=C.ORDSEQ
  AND                 C.ORDSEQ=D.ORDSEQ
  AND                 C.SHPNO=D.SHPNO
  AND                 D.ITMNO=B.ITMNO
  AND                 E.ORDTY IN ('C')
  AND                 E.RELORD(+)=A.ORDSEQ
  AND                 F.ORDSEQ=E.ORDSEQ
  AND                 G.ORDSEQ=E.ORDSEQ
  AND                 G.ITMNO=D.ITMNO
  AND                 G.SHPNO=D.SHPNO
  AND                 G.SHPNO=F.SHPNO
  AND                 G.CLNO=D.CLNO
  AND                 G.SZNO=D.SZNO
  AND                 A.CTRPTY=M0.CODE(+)
  AND                 A.ORIGIN=M1.CODE(+)
  AND                 C.DEST=M2.CODE(+)
  AND                 E.ORDSEQ IS NOT NULL
  AND                 A.ORDNO='SC10-0457'
  GROUP BY
                          A.ORDSEQ, A.ORDNO, A.ORDDT,
                          C.SHPNO, C.SHPDT,
                          D.ITMNO,
                          E.ORDSEQ, E.ORDNO,
                          M0.CODE, M0.LNAME,  M0.CNAME,
                          M1.CODE, M1.LNAME,
                          M2.CODE, M2.LNAME

Answer 2

楼主，从你的语句看，如果你的G.QTY为0的话，那么你所有的数据计算结果必然都为0呀，

Answer 3

试了下 这样也还是不行  SELECT           A.ORDSEQ, A.ORDNO, A.ORDDT,
                          B.SHPNO, B.SHPDT,
                          SUM(NVL((D.QTY*D.PRC*B.EXRT),0)) AMT_HK,
                          SUM(NVL((G.QTY*G.PRC*F.EXRT),0)) AMT_PC
  FROM              ORD A, ORDSHP B, ORDITM C, ORDITMDTL D,
                           ORD E, ORDSHP F, ORDITMDTL G
  WHERE           A.ORDTY IN ('S')
  AND                 A.ORDSEQ=B.ORDSEQ
  AND                 B.ORDSEQ=C.ORDSEQ
  AND                 C.ORDSEQ=D.ORDSEQ
  AND                 C.ITMNO=D.ITMNO
  AND                 D.SHPNO=B.SHPNO
  AND                 E.RELORD=D.ORDSEQ
  AND                 E.ORDTY IN ('C')
  AND                 F.ORDSEQ=E.ORDSEQ
  AND                 F.SHPNO=B.SHPNO
  AND                 G.ORDSEQ=F.ORDSEQ
  AND                 G.SHPNO=F.SHPNO
  AND                 G.ITMNO=D.ITMNO
  AND                 G.CLNO=D.CLNO
  AND                 G.SZNO=D.SZNO
  AND                 A.ORDSEQ=195606
  GROUP BY      A.ORDSEQ, A.ORDNO, A.ORDDT,
                           B.SHPNO, B.SHPDT

Answer 4

经过调试发现：
 去掉  AND                 D.CLNO=G.CLNO
  AND                 D.SZNO=G.SZNO就发现 前面汇总为0的为题解决了
  就应为关联不够 后面的AMT_PC 汇总数据多了
   不知道有什么解决方法没有