VB真的不行吗，那么SQL SERVER应该行吧，一个有点难度的问题，在VFP中可以快速统计，可VB中我做不到，那么用存储过程有没有用，希望大家帮我一下，这是一个有点麻烦的统计!

Question

我把句子写出来大家清楚一点：dim i as long
dim ac as new adodb.connection
dim car as new adodb.recordset
dim custcode as string
dim sm as single
with car 
  .open "select cust_code,cust_name from cust order by cust_code",ac
  for i=1 to .recordcount
    custcode=!cust_code
    '查001
   sm=0
    with qar
      .open"select sum(pack1_number) as sum_number form sale_zhuanzhang where cust_code='" & custcode & "' and pack1_code='001',ac
      if not isnull(!sum_number) then
        sm=sm+!sum_number
      end if
    .close
    .open"select sum(pack2_number) as sum_number form sale_zhuanzhang where cust_code='" & custcode & "' and pack2_code='001',ac
      if not isnull(!sum_number) then
        sm=sm+!sum_number
      end if
    .close
    .open"select sum(pack3_number) as sum_number form sale_zhuanzhang where cust_code='" & custcode & "' and pack3_code='001',ac
      if not isnull(!sum_number) then
        sm=sm+!sum_number
      end if
    .close
   ..............
   ..............
  '依次查003,007,011,014
  '&^%&###*@*@我受不了了!!
  next i
end with

Answer 1

你用以下SQL语句试试：
select cust_name,(select sum(pack1_number) from sale_zhuanzhang where pack1_code='001')+(select sum(pack2_number) from sale_zhuanzhang where pack2_code='001')+(select sum(pack3_number) from sale_zhuanzhang where pack3_code='001') as sum001,
(select sum(pack1_number) from sale_zhuanzhang where pack1_code='003')+(select sum(pack2_number) from sale_zhuanzhang where pack2_code='003')+(select sum(pack3_number) from sale_zhuanzhang where pack3_code='003') as sum003,
......
from cust

Answer 2

select b.cust_name ,
       sum(decode(a.pack1_code,'001',a.ddd,0)),
       sum(decode(a.pack1_code,'003',a.ddd,0)),
       sum(decode(a.pack1_code,'007',a.ddd,0)),
       sum(decode(a.pack1_code,'011',a.ddd,0)),
       sum(decode(a.pack1_code,'014',a.ddd,0))
from 
cust b,
(select cust_code,pack1_code,sum(pack1_number) ddd 
from sale_zhuanzhang 
where pack1_code in (001,003,007,011,014)
group by cust_code,pack1_code
union 
select cust_code,pack2_code,sum(pack2_number) ddd 
from sale_zhuanzhang 
where pack1_code in (001,003,007,011,014)
group by cust_code,pack2_code
union
select cust_code,pack3_code,sum(pack3_number) ddd 
from sale_zhuanzhang 
where pack1_code in (001,003,007,011,014)
group by cust_code,pack3_code
) a
where a.cust_code=b.cust_code以上是ORCALE 的语法，至于SQL SERVER只需将DECODE 换成CASE WHEN
数据库设计需要再斟酌

Answer 3

不好意，偷懒COPY有点错select b.cust_name ,
      sum(decode(a.pack1_code,'001',a.ddd,0)),
      sum(decode(a.pack1_code,'003',a.ddd,0)),
      sum(decode(a.pack1_code,'007',a.ddd,0)),
      sum(decode(a.pack1_code,'011',a.ddd,0)),
      sum(decode(a.pack1_code,'014',a.ddd,0))
from 
cust b,
(select cust_code,pack1_code,sum(pack1_number) ddd 
from sale_zhuanzhang 
where pack1_code in (001,003,007,011,014)
group by cust_code,pack1_code
union 
select cust_code,pack2_code,sum(pack2_number) ddd 
from sale_zhuanzhang 
where pack2_code in (001,003,007,011,014)
group by cust_code,pack2_code
union
select cust_code,pack3_code,sum(pack3_number) ddd 
from sale_zhuanzhang 
where pack3_code in (001,003,007,011,014)
group by cust_code,pack3_code
) a
where a.cust_code=b.cust_code以上是ORCALE 的语法，至于SQL SERVER只需将DECODE 换成CASE WHEN
数据库设计需要再斟酌