日期:2014-05-18  浏览次数:20510 次

高手帮忙一下这个sql数据处理方案
一个采购主表a,采购明细从表aa,一个采购验收主表b,采购验收从表bb。

a: 采购编号a01
aa: 采购编号a01,产品1,数量1,单价1
  采购编号a01,产品2,数量2,单价2
  采购编号a01,产品3,数量3,单价3

a: 采购编号a02
aa: 采购编号a02,产品1,数量1
  采购编号a02,产品2,数量2
  ......


--------------------------------------------------

b: 采购验收编号1
bb: 采购验收编号1,产品1,验收数量1,验收单价1
  采购验收编号1,产品2,验收数量2,验收单价2
  采购验收编号1,产品3,验收数量3,验收单价3

b: 采购验收编号2
bb: 采购验收编号2,产品1,验收数量1,验收单价1
  采购验收编号2,产品2,验收数量2,验收单价2
  ......

1、采购验收的数据来自于采购明细里,一个采购单可以被多个验收单拥有(多次才验收完)。
2、现在想统计出"采购单明细表"里的产品的已验收数量(有多次验收的验收数量要加总计算进来再统计)未验收数量,以采购明细里的产品为主表。
3、可能我表达的还不是很清楚,思路还算是清晰。我用到group by 和left join 始终得不到相要的结果,结果都是好多产品重复,已验收未验收的都全部罗列出来。 呵呵 大家帮帮忙啦,期待高手出手相助啊

想要得到如下效果:

采购单号 产品编号 采购数量 采购单价 已验收数量 验收单价 未验收数量(采购数量-验收数量)
----------------------------------------------------------
  产品1 100 10.00 70 12.00 30
  产品2 200 20.00 100 19.80 100
  ......




------解决方案--------------------
SQL code
create table tba(a01 int)
insert into tba select 1
create table tbaa(a01 int,o1 varchar(20),m1 int,p1 decimal(10,2))
insert into tbaa select 1,'a',20,100.2
insert into tbaa select 1,'b',30,54.23
insert into tbaa select 1,'c',38,123.34
create table tbb(b01 int,o1 varchar(20),m1 int,p1 decimal(10,2))
insert into tbb select 1,'a',17,102.5
insert into tbb select 1,'a',3,105.2  --'a'完成验收
insert into tbb select 1,'b',12,58.5
insert into tbb select 1,'b',7,60.2  --'b'未完成验收
--'c'未有验收
go
select a.a01 采购单号,a.o1 产品编号,a.m1 采购数量,a.p1 采购单价,
isnull(sum(b.m1),0)已验收数量,isnull(sum(b.p1*b.m1)/sum(b.m1),0) as 平均验收单价,a.m1-isnull(SUM(b.m1),0) as 未验收数量
from tbaa a left join tbb b on a.a01=b.b01 and a.o1=b.o1
group by a.a01,a.o1,a.m1,a.p1
/*
采购单号        产品编号                 采购数量        采购单价                                    已验收数量       平均验收单价                                  未验收数量
----------- -------------------- ----------- --------------------------------------- ----------- --------------------------------------- -----------
1           a                    20          100.20                                  20          102.905000                              0
1           b                    30          54.23                                   19          59.126315                               11
1           c                    38          123.34                                  0           0.000000                                38
警告: 聚合或其他 SET 操作消除了 Null 值。

(3 行受影响)

*/
go
drop table tba,tbaa,tbb

------解决方案--------------------
SQL code
create table tba(a01 int)
insert into tba select 1
create table tbaa(a01 int,o1 varchar(20),m1 int,p1 decimal(10,2))
insert into tbaa select 1,'a',20,100.2
insert into tbaa select 1,'b',30,54.23
insert into tbaa select 1,'c',38,123.34
create table tbb(b01 int,o1 varchar(20),m1 int,p1 decimal(10,2))
insert into tbb select 1,'a',17,102.5
insert into tbb select 1,'a',3,105.2  --'a'完成验收
insert into tbb select 1,'b',12,58.5
insert into tbb select 1,'b',7,60.2  --'b'未完成验收
--'c'未有验收
go
select a.a01 采购单号,a.o1 产品编号,a.m1 采购数量,a.p1 采购单价,
isnull(sum(b.m1),0)已验收数量,convert(decimal(10,2),isnull(sum(b.p1*b.m1)/sum(b.m1),0)) as 平均验收单价,a.m1-isnull(SUM(b.m1),0) as 未验收数量
from tbaa a left join tbb b on a.a01=b.b01 and a.o1=b.o1
group by a.a01,a.o1,a.m1,a.p1
/*
采购单号    产品编号             采购数量    采购单价            已验收数量  平均验收单价        未验收数量
----------- -------------------- ----------- ------------------- ----------- ------------------- -----------
1           a                    20          100.20              20          102.91              0
1           b                    30          54.23