34,590
社区成员
发帖
与我相关
我的任务
分享
create table #T(m varchar(5),s varchar(5),c varchar(5), n int)
insert into #T select 'A','B','N',2
union all select 'A','C','M',3
union all select 'A','D','M',2
union all select 'C','C1','N',1
union all select 'C','C2','N',1
union all select 'C','C3','M',1
union all select 'C3','C3A','N',2
union all select 'C3','C3B','N',1
union all select 'D','D1','N',1
union all select 'D','D2','M',1
union all select 'D2','D2A','N',2
go
select c.m,c.s,c.s1,d.s as s2,(case when c.c='M' then c.n*d.n else c.n end) as n
from (
select a.m,a.s,b.s as s1,b.c,(case when a.c='M' then a.n*b.n else a.n end) as n
from (select m,s,c,n from #T where m='A') a left join #T b on a.s=b.m
)c left join #T d on d.m=c.s1
go
drop table #T
/*
m s s1 s2 n
----- ----- ----- ----- -----------
A B NULL NULL 2
A C C1 NULL 3
A C C2 NULL 3
A C C3 C3A 6
A C C3 C3B 3
A D D1 NULL 2
A D D2 D2A 4
(7 行受影响)
*/
create table ss(主件品号 varchar(2),子件品号 varchar(3),类型 varchar(1),数量 varchar(10))
insert into ss
select 'A','B','N',2 union all
select 'A','C','M',3 union all
select 'C','C1','N',1 union all
select 'C','C2','N',1 union all
select 'C','C3','M',1 union all
select 'C3','C3A','N',2 union all
select 'C3','C3B','N',1 union all
select 'A','D','M',2 union all
select 'D','D1','N',1 union all
select 'D','D2','N',1
go
create proc p_ss
as
select id=identity(int,1,1),* into #ss from ss
DECLARE @i int,@j int
set @i=0
set @j=1
select @i=max(id) from #ss
while @j<=@i
begin
update #ss
set 数量=a.数量+'*'+#ss.数量
from #ss inner join #ss a on #ss.主件品号=a.子件品号
where #ss.id=@j
set @j=@j+1
end
select 层=len(数量)-len(replace(数量,'*',''))+1,主件品号,子件品号,数量 from #ss
go
exec p_ss
--结果:
层 主件品号 子件品号 类型 数量
1 A B N 2
1 A C M 3
2 A C1 N 3*1
2 A C2 N 3*1
2 A C3 M 3*1
3 A C3A N 3*1*2
3 A C3B N 3*1*1
1 A D M 2
2 A D1 N 2*1
2 A D2 N 2*1