创建一个方法:用来获取物料对应BOM的所有信息,
drop function f_bom
go
Create Function f_Bom(
@FItemID int
)returns @r table(
FItemID int, --内码ID
FNumber nvarchar(1000), --物料代码
FName nvarchar(1000), --物料名称
FModel nvarchar(1000), --物料规格
level int, --层级
sid varchar(8000) -- 排序字段,通过这个来排序,可以体现树形的层次
)
as
begin
declare @l int
set @l = 0
insert @r select @FItemID,FNumber,FName,FModel,@l,right(10000+FItemID,4) from t_ICItem where FItemID = @FItemID
while @@rowcount > 0
begin
set @l=@l+1
insert @r select i.FItemID,i.FNumber,i.FName,i.FModel,@l,r.sid+','+right(10000+i.FItemID,4)
from t_ICItem i,ICBom b,ICBomChild bc,@r r
Where r.level=@l-1
and r.FItemID = b.FItemID
and b.FInterID = bc.FInterID
and bc.FItemID = i.FItemID
end
return
end
go
select * from f_Bom(9340)
select 层次=space(level*4)+'├─',* from f_bom(9340) order by sid
Select * from ICBom where FBOMNumber = '98.0101010049'
select * from f_Bom(9340)
SQL 执行效果如下: