insert item select 1 , 'A ' , 'A1 '
union all select 2 , 'B ' , 'AAAAA '
union all select 3 , 'A ' , 'AD '
union all select 4 , 'A ' , 'SS '
union all select 5 , 'C ' , '123 '
union all select 6 , 'C ' , 'AAADSFD '
union all select 7 , 'D ' , 'D22 '
union all select 8 , 'C ' , 'DDDD512 '
union all select 9 , 'A ' , 'AA3223 '
union all select 10, 'DD ', '356 '
insert BOM_HEAD select 1,1,1,1, '使用中 '
union all select 2,3,1,1, '使用中 '
union all select 3,1,1,2, '停用 '
union all select 4,6,1,1, '使用中 '
union all select 5,8,1,1, '使用中 '
union all select 6,2,1,1, '使用中 ' --加一条数据
insert BOM_DETAIL select 1, 1,2 ,1
union all select 2, 1,6 ,2
union all select 3, 2,1 ,1
union all select 4, 3,4 ,1
union all select 5, 3,5 ,1
union all select 6, 4,7 ,1
union all select 7, 4,8 ,1
union all select 8, 5,9 ,1
union all select 9, 5,10,1
union all select 10,6,6, 1 --加一条数据
go
--展开bom查询的函数
create function f_bom(
@item int
)returns @r table(
item int,
brand nvarchar(10),
part_no nvarchar(10),
QTY decimal(10,0), --取自BOM_DETAIL
level int, --层次
sid varchar(8000) --排序字段,通过这个来排序,可以体现出树形的层次
)
as
begin
declare @l int
set @l=0
insert @r select @item,brand,part_no,0,@l,right(10000+item,4)
from item
where item=@item
while @@rowcount> 0
begin
set @l=@l+1
insert @r select i.item,i.brand,i.part_no,d.qty,@l,r.sid+ ', '+right(10000+i.item,4)
from item i,BOM_HEAD h,BOM_DETAIL d,@r r
where r.level=@l-1
and r.item=h.MASTER_ITEM
and h.STATUS= '使用中 '
and h.PKID=d.BOM_HEAD_PKID
and d.CHILDREN_ITEM=i.item
end
return
end
go
--调用函数得到查询结果
select 层次=space(level*2)+ '├─ '
,item,brand,part_no,qty
from f_bom(1)
order by sid
go
drop table item,BOM_HEAD,BOM_DETAIL
drop function f_bom
/*--测试结果
层次 item brand part_no qty
-------------- ---------- ---------- ---------- -------
├─ 1 A A1 0
├─ 2 B AAAAA 1
├─ 6 C AAADSFD 1
├─ 7 D D22 1
├─ 8 C DDDD512 1
├─ 9 A AA3223 1
├─ 10 DD 356 1
├─ 6 C AAADSFD 2
├─ 7 D D22 1
├─ 8 C DDDD512 1
├─ 9 A AA3223 1
├─ 10 DD 356 1