一: BOM展开(按任一父结点展开到最底层) 以下写一个简单的,视具体要求稍做修改即可。 create table 表(levelid int,levelname char(2),parent int) insert 表 select 1, 'AA' , 0 union all select 2 , 'BB' , 1 union all select 3 , 'CC' , 1 union all select 4 , 'DD' , 2 union all select 5 , 'EE' , 3 union all select 6 , 'FF', 5
create function bom (@name char(2)) returns @tb table (levelid int,levelname char(2),parent int) as begin insert @tb select levelID,LevelName,parent from 表 where Levelname = @name while @@rowcount > 0 insert @tb select levelID,LevelName,parent from 表 where parent in (select levelID from @tb) and levelID not in (select levelID from @tb) return end
select * from dbo.bom('bb') levelid levelname parent ----------- --------- ----------- 2 BB 1 4 DD 2
(所影响的行数为 2 行)
二: LLC(最低层码) 1:物料主文件中至少有这两个字段 itemNo,llc 2:BOM中至少有这两个字段 (树状) parentItem,itemNo 3: Create Procedure LLC As Update 物料主文件 set llc = 0 --先将LLC全部清为0 Declare @i tinyint Set @i = 0 While @i <= 10 -- 假设BOM最多只有10层 Begin Update a Set a.llc = @i + 1 --子结点的LLC加1 From 物料主文件 a Join bom b on a.itemNo = b.itemNo Join 物料主文件 c on c.itemNo = b.parentItem where c.llc = @i Set @i = @i + 1 End
/*********** Usage: Exec LLC *******/
[ORACLE]ORACLE ERP导数据(BOM清单)
|