27,579
社区成员
发帖
与我相关
我的任务
分享
create table #tb(BOMID int,ParentId varchar(10),BOMDID int,CHILDID varchar(10),QTY int)
insert into #tb
select 1,'A',1,'B',1
union all select 1,'A',2,'C',2
union all select 1,'A',3,'D',2
union all select 1,'A',4,'E',3
union all select 2,'B',1,'F',1
union all select 2,'B',2,'G',8
union all select 2,'B',3,'H',6
union all select 3,'C',1,'M',2
union all select 3,'C',2,'N',3
select parentid,childid,qty
from #tb where bomid=1 --->bomid=1表示根结点
union all
select a.parentid,b.childid,b.qty
from (select * from #tb where bomid=1)a
inner join (select * from #tb where bomid>1)b --->bomid>1表示子结点
on a.childid=b.parentid
drop table #tb
/*
parentid childid qty
----------------------------
A B 1
A C 2
A D 2
A E 3
A F 1
A G 8
A H 6
A M 2
A N 3
*/
如果级别不限制,那可能还要再展开计算