几天都没有解决的问题,请教.谢谢
现有一表 "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 cpspcode,cpscode,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
不能是这样的答案:(不可能把产品名称及倍数这个加入到where后面,
因为关系发生变化,不可能去改代码)
create table tb(autoid int, cpspcode varchar(10), cpscode varchar(10), ipsquantity int)
insert into tb values(1, 'A1 ', 'B ', 2)
insert into tb values(2, 'A1 ', 'C ', 3)
insert into tb values(3, 'B ' , 'D ', 2)
insert into tb values(4, 'A2 ', 'B ', 1)
insert into tb values(5, 'A2 ', 'C ', 2)
select * from
(
select cpspcode, cpscode ,ipsquantity from tb where cpscode = 'C '
union all
select t2.cpspcode , cpscode = 'D ' , t1.ipsquantity*t2.ipsquantity ipsquantity from
(
select * from tb where cpspcode = 'B '
) t1,
(
select * from tb where cpscode = 'B '
) t2
) t
order by cpspcode, cpscode
drop table tb
/*
cpspcode cpscode ipsquantity
---------- ---------- -----------
A1 C 3
A1 D 4
A2 C 2
A2 D 2
（所影响的行数为 4 行）
*/
[解决办法]
就是说只要不是一个完整的产品就去掉？
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 where cpspcode not in(select cpscode 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
4 A2 D 2
5 A2 C 2
(4 row(s) affected)
*/
