# 求BOM子阶id和对应的最上层的父阶id的对应关系的存储过程

zhrongr 2015-07-30 05:21:27

bom表
partID(子阶) parentID（父阶） yl（用量）
2005 1001 1
2006 2007 1
2007 1002 2
4301 3021 2
4301 4001 3
3021 1002 2
4001 1005 1

product表
ID（部件id） name（部件名称）
2005 G
2006 H
2007 I
4301 D
3021 E
4001 F
1001 A
1002 B
1005 C

partID(子阶) parentID（父阶） yl（用量） name（部件名称）
2005 1001 1 G
2006 1002 2 H
2007 1002 1 I
4301 1002 4 D
3021 1002 2 E
4301 1005 3 D
4001 1005 1 F
...全文
258 5 点赞 打赏 收藏 举报

5 条回复

zhrongr 2015-12-21

• 打赏
• 举报

zhrongr 2015-12-21

• 打赏
• 举报

chg198455 2015-09-21

• 打赏
• 举报

• 打赏
• 举报

CZP98168 2015-08-04

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
• 打赏
• 举报

2.1w+

MS-SQL Server 疑难问题

2015-07-30 05:21