原帖地址
http://community.csdn.net/expert/topic/3384/3384941.xml?temp=.6640436
-------------------------------------------------------------------------
--測試數據
create table [dbo].[bom_detail] (
[pkid] [int] ,--identity (1, 1) not null ,
[bom_head_pkid] [int] not null ,
[children_item] [int] not null ,
[qty] [decimal](10, 0) not null
) on [primary]
create table [dbo].[bom_head] (
[pkid] [int] ,--identity (1, 1) not null ,
[master_item] [int] not null ,
[qty] [int] not null ,
[ver] [char] (20) collate chinese_prc_ci_as not null ,
[status] [nvarchar] (10) collate chinese_prc_ci_as not null ,
) on [primary]
create table [dbo].[item] (
[item] [int] ,--identity (1, 1) not null ,
[brand] [nvarchar] (10) ,
[part_no] [nvarchar] (10)
) on [primary]
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_head表中pkid為一唯一值,同一master_item的status只有一行為"使用中",別的都為"停用"(注:是同一master_item),通過master_item與表item的item相關聯
bom_detail表中通過bom_head_pkid與bom_head表相關聯,通過chidern_item與item表的item相關聯
--*/
/*--展開bom的說明
item為1的物料的bom組成情況:
到bom_head表中查找master_item=1 and status='使用中'
這樣可以找到bom_head表中的pkid=1的記錄
我可以從bom_detail表中根據bom_head_pkid=1可以得到master_item這個物料需要用到
childern_item分別為2 和 6 的物料;
在bom_head中master_item值為6并且status='使用中'
這樣可以到到bom_head的pkid為4
然后再到bom_detail中找到bom_head_pkid=4的記錄,這樣就可以發現master_item為6的物料需要用到childern_item分別為7和8的物料;
在bom_head中master_item值為8并且status='使用中'
這樣可以到到bom_head的pkid為45
然后再到bom_detail中找到bom_head_pkid=5的記錄,這樣就可以發現master_item為8的物料需要用到childern_item為9和10的物料;
這樣依次類推
最后要得到一個類示樹狀的結構
如下圖所示
第一層 1 brand,part_no
第二層 2 brand,part_no,qty 6 brand,part_no,qty
第三層 7 brand,part_no,qty 8 brand,part_no,qty
第四層 9 brand,part_no,qty 10 brand,part_no,qty
--*/
--展開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 [email protected]
while @@rowcount>0
begin
set @[email protected]+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 [email protected]
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
（所影響的行數為 12 行）
--*/
