请教中!谢谢.望好心人能帮忙
现有一表 "productstructuresEX "为产品结构表.
假如有两产品结构为:
一个A1需要两个B三个C,一个B需要两个D;
一个A2需要一个B两个C,一个B需要两个D
则在表里列示为:
autoid cpspcode cpscode ipsquantity
1 A1 B 2
2 A1 C 3
3 B D 2
4 A2 B 1
5 A2 C 2
现有一代码计算子件的需求数:
begin
declare @i int,@ret varchar(8000)
set @i = 1
select a.cpspcode,a.cpscode,a.ipsquantity as ipsquantity,@i as level into #t
from productstructuresEX
while @@rowcount <> 0
begin
set @i = @i + 1
print @i
insert into #t
select
a.cpspcode,a.cpscode,a.ipsquantity*B.ipsquantity,@i
from
productstructuresEX a,#t b
where
a.cpspcode=b.cpscode and b.Level = @i-1
end
delete t from #t t where exists(select 1 from #t where cpspcode=t.cpscode)
select cpscode,sum(ipsquantity) as 需求数 into #b from #t GROUP BY cpscode
end
结果:
这样算出来的结果为:
cpscode ipsquantity
D 6
C 5
现想要一个这样的结果:
cpspcode cpscode ipsquantity
A1 D 4
A1 C 3
A2 D 2
A2 C 2
请教中!谢谢
[解决办法]
楼主发了多少帖啊，这分我通通接啦，哈哈
create table productstructuresEX(autoid int,cpspcode varchar(10),cpscode varchar(10),ipsquantity int)
insert into productstructuresEX values(1, 'A1 ', 'B ',2)
insert into productstructuresEX values(2, 'A1 ', 'C ',3)
insert into productstructuresEX values(3, 'B ' , 'D ',2)
insert into productstructuresEX values(4, 'A2 ', 'B ',1)
insert into productstructuresEX values(5, 'A2 ', 'C ',2)
select * into #t from productstructuresEX
while @@rowcount > 0
update t
set t.cpscode = p.cpscode,t.ipsquantity = t.ipsquantity*p.ipsquantity
from #t t
join productstructuresEX p on p.cpspcode = t.cpscode
select * from #t
go
drop table productstructuresEX,#t
/*
autoid cpspcode cpscode ipsquantity
----------- ---------- ---------- -----------
1 A1 D 4
2 A1 C 3
3 B D 2
4 A2 D 2
5 A2 C 2
(5 row(s) affected)
*/
