22,302
社区成员




declare @bom table(
partid int,
parentid int,
yl int
)
declare @product table(
id int,
name nvarchar(20)
)
declare @bomtree table(
partid int,
parentid int,
topid int, --顶级id
ilevel int, --级次
yl int
)
--级次变量,初始化为1
declare @ilevel int
set @ilevel = 1
--测试数据
insert into @bom
select
2005, 1001, 1
union
select
2006, 2007, 1
union
select
2007, 1002, 2
union
select
4301, 3021, 2
union
select
4301, 4001, 3
union
select
3021, 1002, 2
union
select
4001, 1005, 1
insert into @product
select
2005, 'G'
union
select
2006, 'H'
union
select
2007, 'I'
union
select
4301, 'D'
union
select
3021, 'E'
union
select
4001, 'F'
union
select
1001, 'A'
union
select
1002, 'B'
union
select
1005, 'C'
--从顶级开始推算
insert into @bomtree
select distinct
parentid,
null,
parentid,
@ilevel,
null
from @bom
where parentid not in (
select partid
from @bom
)
while exists(
select top 1 1001
from @bomtree
where partid in (
select
parentid
from @bom
)
and ilevel = @ilevel
)
begin
insert into @bomtree
select distinct
b.partid,
b.parentid,
t.topid,
@ilevel + 1,
b.yl
from @bom b
inner join @bomtree t
on t.partid = b.parentid
and t.ilevel = @ilevel
set @ilevel = @ilevel + 1
end
select
b.partid,
b.topid,
p.name,
b.yl
from @bomtree b
inner join @product p
on p.id = b.partid
where b.ilevel > 1
order by topid,ilevel
partid topid name yl
----------- ----------- -------------------- -----------
2005 1001 G 1
2007 1002 I 2
3021 1002 E 2
2006 1002 H 1
4301 1002 D 2
4001 1005 F 1
4301 1005 D 3