展开BOM

王朝other·作者佚名  2006-01-09
窄屏简体版  字體: |||超大  

原帖地址

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 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

(所影响的行数为 12 行)

--*/

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
 
© 2005- 王朝網路 版權所有 導航