物料清单的问题(BOM表)

[GDTOPONE]

前题:
1.一件产品有数个子件所组成.
2.子件分为两类(A:单纯是子件,B:子件又由其它子件所组成)

数据结构:
产品表:PRCA
PRAON:产品编号.
PRCAID:对应的子件编码

子件表:PRCB
PRBCON 子件编号
PRCBID 与产品表的子件编码对应
PRCCID 这子件的子件编码(当不为NULL时表示这子件又由其它子件所组成),与表的PRCBID字段对应

/*数据如下:
PRCA表:
PRAON PRCAID
090302 cad-213-415
09201 FA423-214-456

PRCB表:
PRBCON PRCBID PRCCID
0010052 cad-213-415 FA423-214-456
0080024 FA423-214-456
023145 cad-213-415
074394 FA423-214-456
008456 L310456-789
*/

问题:如果实现当输入一个产品编号得出此产品所组的所有子件?例如:
当输入:090302 这个产品得出结果如下:
/*
PRBCON PRCBID
0010052 cad-213-415
0080024 FA423-214-456
023145 cad-213-415
074394 FA423-214-456
*/



[/code]

[水族杰纶]

关注并学习~~~

[hyde100]

连接查询，代码待会贴上

[hery2002]

--这种效果 :)

--BOM算法
--产品配件清单查询示例(邹建)
CREATE TABLE Item(ID int,Name varchar(10),Wast decimal(2,2))
INSERT Item SELECT 1,N'A产品',0.01
UNION  ALL  SELECT 2,N'B产品',0.02
UNION  ALL  SELECT 3,N'C产品',0.10
UNION  ALL  SELECT 4,N'D配件',0.15
UNION  ALL  SELECT 5,N'E物料',0.03
UNION  ALL  SELECT 6,N'F物料',0.01
UNION  ALL  SELECT 7,N'G配件',0.02

CREATE TABLE Bom(ItemID int,ChildId int)
INSERT Bom SELECT 1,4
UNION  ALL SELECT 1,7   --A产品由D配件和G配件组成
UNION  ALL SELECT 2,1
UNION  ALL SELECT 2,6
UNION  ALL SELECT 2,7   --B产品由F物料及G配件组成
UNION  ALL SELECT 4,5
UNION  ALL SELECT 4,6    --D配件由F物料组成
UNION  ALL SELECT 3,2
UNION  ALL SELECT 3,1    --C产品由A产品和B产品组成
GO

CREATE FUNCTION f_Bom(
@ItemIDs varchar(1000), --要查询物料清单及生产量的产品编号列表(逗号分隔)
@Num   int          --要生产的数量
)RETURNS @t TABLE(ItemID int,ChildId int,Nums int,Level int)
AS
BEGIN
    DECLARE @Level int
    SET @Level=1
    INSERT @t SELECT a.ItemID,a.ChildId,ROUND(@Num/(1-b.Wast),0),@Level
    FROM Bom a,Item b
    WHERE a.ChildId=b.ID
        AND CHARINDEX(','+RTRIM(a.ItemID)+',',','+@ItemIDs+',')>0
    WHILE @@ROWCOUNT>0 and @Level<140
    BEGIN
        SET @Level=@Level+1
        INSERT @t SELECT a.ItemID,b.ChildId,ROUND(a.Nums/(1-c.Wast),0),@Level
        FROM @t a,Bom b,Item c
        WHERE a.ChildId=b.ItemID
            AND b.ChildId=c.ID
            AND a.Level=@Level-1
    END
    RETURN
END
GO

--调用函数展开产品1、2、3的结构及计算生产10个产品时,各需要多少个配件
SELECT a.ItemID,ItemName=b.Name,
    a.ChildId,ChildName=c.Name,
    a.Nums,a.Level
FROM f_Bom('1,2,3',10) a,Item b,Item c
WHERE a.ItemID=b.ID
    AND a.ChildId=c.ID
ORDER BY a.ItemID,a.Level,a.ChildId

/*
ItemID      ItemName   ChildId     ChildName  Nums        Level
----------- ---------- ----------- ---------- ----------- -----------
1           A产品        4           D配件        12          1
1           A产品        7           G配件        10          1
1           A产品        5           E物料        12          2
1           A产品        6           F物料        12          2
2           B产品        1           A产品        10          1
2           B产品        6           F物料        10          1
2           B产品        7           G配件        10          1
2           B产品        4           D配件        12          2
2           B产品        7           G配件        10          2
2           B产品        5           E物料        12          3
2           B产品        6           F物料        12          3
3           C产品        1           A产品        10          1
3           C产品        2           B产品        10          1
3           C产品        1           A产品        10          2
3           C产品        4           D配件        12          2
3           C产品        6           F物料        10          2
3           C产品        7           G配件        10          2
3           C产品        7           G配件        10          2
3           C产品        4           D配件        12          3
3           C产品        5           E物料        12          3
3           C产品        6           F物料        12          3
3           C产品        7           G配件        10          3
3           C产品        5           E物料        12          4
3           C产品        6           F物料        12          4

(24 row(s) affected)


*/
drop table item
drop table bom
drop function f_Bom

[Garnett_KG]

2005

DECLARE  @PRCA TABLE( PRAON VARCHAR(15),PRCAID VARCHAR(30))
INSERT INTO @PRCA SELECT
'090302' ,     'cad-213-415'
UNION ALL SELECT 
'09201'  ,      'FA423-214-456'

DECLARE @PRCB TABLE (PRBCON VARCHAR(15),PRCBID VARCHAR(30),PRCCID VARCHAR(30))
INSERT INTO @PRCB SELECT 
'0010052' , 'cad-213-415' ,   'FA423-214-456'
UNION ALL SELECT 
'0080024' , 'FA423-214-456',NULL
UNION ALL SELECT 
'023145'  ,  'cad-213-415',NULL
UNION ALL SELECT 
'074394'  ,  'FA423-214-456',NULL
UNION ALL SELECT 
'008456' ,   'L310456-789',NULL
DECLARE @ID VARCHAR(15)
SET @ID='090302'
;
WITH BOM 
AS
(
	SELECT PRBCON,PRCBID,PRCCID FROM @PRCB WHERE PRCBID=(SELECT TOP 1 PRCAID FROM @PRCA WHERE PRAON=@ID)
	UNION ALL
	SELECT a.PRBCON,a.PRCBID,a.PRCCID
		 
	FROM	@PRCB a INNER JOIN BOM b 
		ON a.PRCBID=b.PRCCID
)
SELECT * FROM BOM

[rhq12345]

declare @PRAON      varchar(999),@PRCAID varchar(999)
set @PRBCON   ='090302 '
select top 1 @PRBCON=PRBCON    from PRCA where PRCAID=@PRCAID

select *  into #result from PRCB where PRCBID =@PRCAID
while exists(select 1 from PRCB where PRCBID in(select PRBCID from #result)   )
bengin 
insert into #result
select *  where PRCBID in(select PRBCID from #result)
end


select PRBCON,PRCBID from #result

[fa_ge]

http://blog.csdn.net/fa_ge/archive/2007/11/21/1895742.aspx

[hyde100]

create table #PRCA
(
PRAON nvarchar(50) ,
PRCAID nvarchar(50)
)

create table #PRCB
(
PRBCON nvarchar(50),
PRCBID nvarchar(50),
PRCCID nvarchar(50)
)

INSERT INTO #PRCA VALUES('090302','cad-213-415 ')
INSERT INTO #PRCA VALUES ('09201','FA423-214-456 ')

INSERT INTO #PRCB VALUES('0010052', 'cad-213-415', 'FA423-214-456')
INSERT INTO #PRCB VALUES('0080024', 'FA423-214-456',NULL)
INSERT INTO #PRCB VALUES('023145', 'cad-213-415' ,NULL)
INSERT INTO #PRCB VALUES('074394', 'FA423-214-456',NULL)
INSERT INTO #PRCB VALUES('008456', 'L310456-789' ,NULL)


SELECT #PRCB.PRBCON,#PRCB.PRCBID
FROM #PRCA
LEFT OUTER JOIN #PRCB ON #PRCB.PRCBID =#PRCA. PRCAID
--LEFT OUTER JOIN #PRCB A ON A.PRCBID = #PRCB.PRCCID
WHERE PRAON ='090302'
UNION
SELECT A.PRBCON,A.PRCBID
FROM #PRCA
LEFT OUTER JOIN #PRCB ON #PRCB.PRCBID =#PRCA. PRCAID
LEFT OUTER JOIN #PRCB A ON A.PRCBID = #PRCB.PRCCID
WHERE PRAON ='090302' AND #PRCB.PRCCID IS NOT NULL


DROP TABLE #PRCA
DROP TABLE #PRCB

------------------------------------
0010052 cad-213-415
0080024 FA423-214-456
023145 cad-213-415
074394 FA423-214-456

[hyde100]

DECLARE @PRAON nvarchar(50)
SET @PRAON = '090302'

create table #PRCA
(
PRAON nvarchar(50) ,
PRCAID nvarchar(50)
)

create table #PRCB
(
PRBCON nvarchar(50),
PRCBID nvarchar(50),
PRCCID nvarchar(50)
)

INSERT INTO #PRCA VALUES('090302','cad-213-415 ')
INSERT INTO #PRCA VALUES ('09201','FA423-214-456 ')

INSERT INTO #PRCB VALUES('0010052', 'cad-213-415', 'FA423-214-456')
INSERT INTO #PRCB VALUES('0080024', 'FA423-214-456',NULL)
INSERT INTO #PRCB VALUES('023145', 'cad-213-415' ,NULL)
INSERT INTO #PRCB VALUES('074394', 'FA423-214-456',NULL)
INSERT INTO #PRCB VALUES('008456', 'L310456-789' ,NULL)


SELECT #PRCB.PRBCON,#PRCB.PRCBID
FROM #PRCA
LEFT OUTER JOIN #PRCB ON #PRCB.PRCBID =#PRCA. PRCAID
WHERE PRAON =@PRAON
UNION
SELECT A.PRBCON,A.PRCBID
FROM #PRCA
LEFT OUTER JOIN #PRCB ON #PRCB.PRCBID =#PRCA. PRCAID
LEFT OUTER JOIN #PRCB A ON A.PRCBID = #PRCB.PRCCID
WHERE PRAON =@PRAON AND #PRCB.PRCCID IS NOT NULL


DROP TABLE #PRCA
DROP TABLE #PRCB

[brother2605]

/*
问题:如果实现当输入一个产品编号得出此产品所组的所有子件?例如:
当输入:090302 这个产品得出结果如下:

PRBCON PRCBID
0010052 cad-213-415
0080024 FA423-214-456
023145 cad-213-415
074394 FA423-214-456
*/

--用递归查询
create table [dbo].[PRCA](PRAON varchar (20),PRCAID varchar (20))
go
insert into [dbo].[PRCA] values('090302','cad-213-415')
insert into [dbo].[PRCA] values('09201','FA423-214-456')
go
create table [dbo].[PRCB](PRBCON varchar(20),PRCBID varchar(20),PRCCID varchar(20) null)
go
insert into [dbo].[PRCB] values('0010052','cad-213-415','FA423-214-456')
insert into [dbo].[PRCB] values('0080024','FA423-214-456',null)
insert into [dbo].[PRCB] values('023145','cad-213-415',null)
insert into [dbo].[PRCB] values('074394','FA423-214-456',null)
insert into [dbo].[PRCB] values('008456','L310456-789',null)

go

declare @vPID varchar(20)
set @vPID='090302';

with t_result(PRBCON,PRCBID,PRCCID)
as
(
select a.PRBCON,a.PRCBID,a.PRCCID
from [dbo].[PRCB] a inner join [dbo].[PRCA] b on a.PRCBID=b.PRCAID
where b.PRAON=@vPID
union all
select a.PRBCON,a.PRCBID,a.PRCCID
from [dbo].[PRCB] a inner join t_result b on a.PRCBID=b.PRCCID
)
select*from t_result


drop table [dbo].[PRCA]
drop table [dbo].[PRCB]

/*结果
PRBCON PRCBID PRCCID
0010052 cad-213-415 FA423-214-456
023145 cad-213-415 NULL
0080024 FA423-214-456 NULL
074394 FA423-214-456 NULL

*/

[fuxiaoyang13]

顶顶！！