create table #(autoid int identity,cpspcode nvarchar(10), cpscode nvarchar(10),ipsquantity int)
insert into #
select 'A1','B',2 union all
select 'A1','C',3 union all
select 'B','D',2 union all
select 'A2','B',1 union all
select 'A2','C',2
goselect cpspcode,cpscode = (case cpscode when 'B' then 'D' else cpscode end),
ipsquantity = (case when cpscode = 'B' then (select ipsquantity * 2 from # where cpspcode = 'B') else ipsquantity end) from #
where cpspcode like 'A%'/*
cpspcode cpscode ipsquantity
A1 D 4
A1 C 3
A2 D 2
A2 C 2
*/
insert into #
select 'A1','B',2 union all
select 'A1','C',3 union all
select 'B','D',2 union all
select 'A2','B',1 union all
select 'A2','C',2
goselect cpspcode,cpscode = (case cpscode when 'B' then 'D' else cpscode end),
ipsquantity = (case when cpscode = 'B' then (select ipsquantity * 2 from # where cpspcode = 'B') else ipsquantity end) from #
where cpspcode like 'A%'/*
cpspcode cpscode ipsquantity
A1 D 4
A1 C 3
A2 D 2
A2 C 2
*/
原来
-----
这应该是一个树形结构,你能否把你表中可能出现的情况给全面些?还有对你下面说的,有没有什么规则?
----------------------------------------------------------
现有一表"productstructuresEX"为产品结构表.
假如有两产品结构为: 一个A1需要两个B三个C,一个B需要两个D;
一个A2需要一个B两个C,一个B需要两个D
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 productstructuresEXwhile @@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 #tgo
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)
*/
autoid cpspcode cpscode ipsquantity
3 B D 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 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 #tgo
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)
*/