还是那SQl语句....

Question

SELECT o.*, p.垫付款未收已付出票 AS 垫付款未收已付出票
FROM (SELECT m.*, n.授信余额 AS 授信余额, n.授信总额 AS 授信总额
        FROM (SELECT k.*, l.垫付款已收已付 AS 垫付款已收已付
                FROM (SELECT i.*, j.垫付款已收未付 AS 垫付款已收未付
                        FROM (SELECT g.*, 
                                      h.垫付款未收已付未出票 AS 垫付款未收已付未出票
                                FROM (SELECT e.*, 
                                              f.垫付款未收未付 AS 垫付款未收未付
                                        FROM (SELECT c.*, 
                                                      d .代收货款未收 AS 代收货款未收
                                                FROM (SELECT a.*, 
                                                              b.代收货款已收 AS 代收货款已收
                                                        FROM (SELECT 目的地, 
                                                                      收货人姓名 AS 客户名, 
                                                                      SUM(总体积) AS 方量, 
                                                                      SUM(总重量) AS 吨数, 
                                                                      SUM(总运费) AS 总运费, 
                                                                      (SUM(总运费) 
                                                                      - SUM(实收运费)) 
                                                                      AS 未收运费
                                                                FROM 运单表
                                                                WHERE (开单日期 > '2006 - 3 - 1')
                                                                GROUP BY 目的地, 收货人姓名) 
                                                              a LEFT OUTER JOIN
                                                                  (SELECT 目的地, 
                                                                       收货人姓名 AS 客户名, 
                                                                       SUM(货款) 
                                                                       AS 代收货款已收
                                                                 FROM 运单表
                                                                 WHERE (开单日期 > '2006 - 3 - 1')
                                                                        AND (货款类型 = '代收') 
                                                                       AND (实收货款总额 > 0)
                                                                 GROUP BY 目的地, 
                                                                       收货人姓名) b ON 
                                                              a.目的地 = b.目的地 AND 
                                                              a.客户名 = b.客户名) 
                                                      c LEFT OUTER JOIN
                                                          (SELECT 目的地, 
                                                               收货人姓名 AS 客户名, 
                                                               SUM(货款) 
                                                               AS 代收货款未收
                                                         FROM 运单表
                                                         WHERE (开单日期 >' 2006 - 3 - 1') AND 
                                                               (货款类型 = '代收') AND 
                                                               (实收货款总额 = 0)
                                                         GROUP BY 目的地, 收货人姓名) d ON 
                                                      c.目的地 = d .目的地 AND 
                                                      c.客户名 = d .客户名) 
                                              e LEFT OUTER JOIN
                                                  (SELECT 目的地, 收货人姓名 AS 客户名, 
                                                       SUM(货款) AS 垫付款未收未付
                                                 FROM 运单表
                                                 WHERE (开单日期 >' 2006 - 3 - 1') AND 
                                                       (实收货款总额 = 0) AND (LEFT(货款类型, 
                                                       2) = ' 垫付 ') AND (实付金额 = 0)
                                                 GROUP BY 目的地, 收货人姓名) f ON 
                                              e.目的地 = f.目的地 AND e.客户名 = f.客户名) 
                                      g LEFT OUTER JOIN
                                          (SELECT 目的地, 收货人姓名 AS 客户名, SUM(货款) 
                                               AS 垫付款未收已付未出票
                                         FROM 运单表
                                         WHERE (开单日期 >' 2006 - 3 - 1') AND 
                                               (实收货款总额 = 0) AND (LEFT(货款类型, 2) 
                                               = '垫付') AND (实付金额 > 0) AND 
                                               (支付日期 > getdate() - 25)
                                         GROUP BY 目的地, 收货人姓名) h ON 
                                      g.目的地 = h.目的地 AND g.客户名 = h.客户名) 
                              i LEFT OUTER JOIN
                                  (SELECT 目的地, 收货人姓名 AS 客户名, SUM(货款) 
                                       AS 垫付款已收未付
                                 FROM 运单表
                                 WHERE (开单日期 > '2006 - 3 - 1') AND (实收货款总额 > 0) AND
                                        (LEFT(货款类型, 2) = '垫付') AND (实付金额 = 0)
                                 GROUP BY 目的地, 收货人姓名) j ON 
                              i.目的地 = j.目的地 AND i.客户名 = j.客户名) 
                      k LEFT OUTER JOIN
                          (SELECT 目的地, 收货人姓名 AS 客户名, SUM(货款) 
                               AS 垫付款已收已付
                         FROM 运单表
                         WHERE (开单日期 > '2006 - 3 - 1') AND (实收货款总额 > 0) AND 
                               (LEFT(货款类型, 2) = '垫付') AND (实付金额 > 0)
                         GROUP BY 目的地, 收货人姓名) l ON k.目的地 = l.目的地 AND 
                      k.客户名 = l.客户名) m LEFT OUTER JOIN
                  (SELECT 联系人 AS 客户名, 所在地 AS 目的地, (最高授信 - 已用授信) 
                       AS 授信余额, 最高授信 AS 授信总额
                 FROM 客户表) n ON m.客户名 = n.客户名 AND m.目的地 = n.目的地) 
      o LEFT OUTER JOIN
          (SELECT 目的地, 收货人姓名 AS 客户名, SUM(货款) AS 垫付款未收已付出票
         FROM  运单表 where (开单日期 > '2006 - 3 - 1') AND (实收货款总额 = 0) AND 
               (LEFT(货款类型, 2) = '垫付') AND (实付金额 > 0) AND (支付日期 < getdate() - 25)
         GROUP BY 目的地, 收货人姓名) p ON o.客户名 = p.客户名 AND 
      o.目的地 = p.目的地
WHERE 1=1
ORDER BY p.目的地, p.客户名
用下面的语句求和,会不会出问题:
  try
        while not adoquery1.eof do
        begin
         jh:=adoquery1.fieldbyname('方量').Asinteger;
         yj:=adoquery1.fieldbyname('总运费').AsFloat;
         ds:=adoquery1.fieldbyname('吨数').AsFloat;
         cp:=adoquery1.fieldbyname('垫付款未收已付出票').AsFloat;
         wdx:=adoquery1.fieldbyname('垫付款未收已付未出票').AsFloat;
         j:=0;
         y:=0;
         d:=0;
         c:=0;
         w:=0;
         j:=j+jh;
         y:=y+yj;
         d:=d+ds;
         c:=c+cp;
         w:=w+wdx;
         adoquery1.next;
      end;等等去截个图   把我想要的结果帖出来 

Answer 1

用while得出了结果和在企业管理器上得出来的差了 N 多

Answer 2

分很多，但SQL太长了：（帮你顶一下吧

Answer 3

with adoquery1 do
begin
        j:=0; 
        y:=0; 
        d:=0; 
        c:=0; 
        w:=0;   First;
  while not eof do 
  begin 
        jh:=fieldbyname('方量').Asinteger; 
        yj:=fieldbyname('总运费').AsFloat; 
        ds:=fieldbyname('吨数').AsFloat; 
        cp:=fieldbyname('垫付款未收已付出票').AsFloat; 
        wdx:=fieldbyname('垫付款未收已付未出票').AsFloat; 
        j:=j+jh; 
        y:=y+yj; 
        d:=d+ds; 
        c:=c+cp; 
        w:=w+wdx; 
        next; 
  end; 不想说什么，你看看就明白了
自己对齐一下

Answer 4

初始化数据怎么放在循环体内?这样每循环一次,j,y,d,c,w又会变为0了,怎么能统计?
这里肯定是须要改的,像4楼的就行

Answer 5

本来看了你的题目想进来帮你，但看了你的SQL，只想吐。有够繁锁的了。

Answer 6

你嵌套的也够深的了while not adoquery1.eof do 
        begin 
        jh:=adoquery1.fieldbyname('方量').Asinteger; 
        yj:=adoquery1.fieldbyname('总运费').AsFloat; 
        ds:=adoquery1.fieldbyname('吨数').AsFloat; 
        cp:=adoquery1.fieldbyname('垫付款未收已付出票').AsFloat; 
        wdx:=adoquery1.fieldbyname('垫付款未收已付未出票').AsFloat;       
        j:=j+jh; 
        y:=y+yj; 
        d:=d+ds; 
        c:=c+cp; 
        w:=w+wdx; 
        adoquery1.next;

Answer 7

为什么没有采纳我的意见呢？
从中间的sql语句依次往外边走，逐句测试应该能找到问题的所在的

Answer 8

  j:=0; 
  y:=0; 
  d:=0; 
  c:=0; 
  w:=0;   First; 
  while not adoquery1.eof do 
  begin 
        jh:= jh + adoquery1.fieldbyname('方量').Asinteger; 
        yj:= yj + adoquery1.fieldbyname('总运费').AsFloat; 
        ds:= ds + adoquery1.fieldbyname('吨数').AsFloat; 
        cp:= cp + adoquery1.fieldbyname('垫付款未收已付出票').AsFloat; 
        wdx:= wdx +adoquery1.fieldbyname('垫付款未收已付未出票').AsFloat; 
        adoquery1.next; 
  end;