BOM問題,求原材料的頂層物料.

[skyinfo]

--表及測試數據如下:

--表1:bas_part(物料表)
/*
物料編碼(invid)說明:以04開頭的=成品;以03開頭的=半成品;以01開頭的=原材料
*/
IF EXISTS (SELECT * FROM sysobjects WHERE name='bas_part' AND xtype='U')
DROP TABLE bas_part
GO

CREATE TABLE bas_part(
partid NUMERIC(10,0) NOT NULL, /*自動編號*/
invid VARCHAR(50) NULL, /*存貨編碼*/
invname VARCHAR(50) NULL,
CONSTRAINT pk_bas_part PRIMARY KEY(partid)
)
GO

--表2 bom(bom表)
IF EXISTS (SELECT * FROM sysobjects WHERE name='bom' AND xtype='U')
DROP TABLE bom
GO

CREATE TABLE bom(
rkey NUMERIC(10,0) IDENTITY(1,1) NOT NULL, /*自動編號*/
ancesid NUMERIC(10,0) NULL, /*最頂層物料id*/
parentid NUMERIC(10,0) NULL, /*母件物料id*/
compid NUMERIC(10,0) NULL, /*子件物料id*/
CONSTRAINT pk_bom PRIMARY KEY(rkey)
)
GO

INSERT INTO bas_part
SELECT 1,'0401','成品1'
UNION ALL SELECT 2,'0402','成品2'
UNION ALL SELECT 3,'0301','半成品1'
UNION ALL SELECT 4,'0302','半成品2'
UNION ALL SELECT 5,'0303','半成品3'
UNION ALL SELECT 6,'0304','半成品4'
UNION ALL SELECT 7,'0305','半成品5'
UNION ALL SELECT 9,'0101','原材料1'
UNION ALL SELECT 10,'0102','原材料2'
UNION ALL SELECT 11,'0103','原材料3'
UNION ALL SELECT 12,'0104','原材料4'

INSERT INTO bom
SELECT 1,3,4
UNION ALL SELECT 1,4,5
UNION ALL SELECT 1,5,12
UNION ALL SELECT 2,2,10
UNION ALL SELECT 2,6,11


--SELECT * FROM bom
/*
形成的物料清單如下:
A)
成品1
├半成品1
├半成品2
├半成品3
├原材料4

B)
成品2
├原材料2
├半成品4
├原料3

現求:

現求原材料的最頂層的半成品或者成品。
如原材料最頂層沒有半成品，則它的成品就是最頂層，如上B中"原料2"就對應“半成品2”;
如原材料的最頂層是有半品，哪麼就是半成品，如A中,"原材料4"就對應“半成品1”,B中"原料3"就對應“半成品4”

要得到的表如下:
成品名稱 原料名稱 頂層原料
----------------------------------
成品1 原材料4 半成品1
成品2 原材料3 半成品4
成品2 原材料2 成品2

*/

[skyinfo]

--表1:bas_part(物料表)
/*
物料編碼(invid)說明:以04開頭的=成品;以03開頭的=半成品;以01開頭的=原材料
*/
IF EXISTS (SELECT * FROM sysobjects WHERE name='bas_part' AND xtype='U')
DROP TABLE bas_part
GO

CREATE TABLE bas_part(
partid NUMERIC(10,0) NOT NULL, /*自動編號*/
invid VARCHAR(50) NULL, /*存貨編碼*/
invname VARCHAR(50) NULL,
CONSTRAINT pk_bas_part PRIMARY KEY(partid)
)
GO

--表2 bom(bom表)
IF EXISTS (SELECT * FROM sysobjects WHERE name='bom' AND xtype='U')
DROP TABLE bom
GO

CREATE TABLE bom(
rkey NUMERIC(10,0) IDENTITY(1,1) NOT NULL, /*自動編號*/
ancesid NUMERIC(10,0) NULL, /*最頂層物料id*/
parentid NUMERIC(10,0) NULL, /*母件物料id*/
compid NUMERIC(10,0) NULL, /*子件物料id*/
CONSTRAINT pk_bom PRIMARY KEY(rkey)
)
GO

INSERT INTO bas_part
SELECT 1,'0401','成品1'
UNION ALL SELECT 2,'0402','成品2'
UNION ALL SELECT 3,'0301','半成品1'
UNION ALL SELECT 4,'0302','半成品2'
UNION ALL SELECT 5,'0303','半成品3'
UNION ALL SELECT 6,'0304','半成品4'
UNION ALL SELECT 7,'0305','半成品5'
UNION ALL SELECT 9,'0101','原材料1'
UNION ALL SELECT 10,'0102','原材料2'
UNION ALL SELECT 11,'0103','原材料3'
UNION ALL SELECT 12,'0104','原材料4'

INSERT INTO bom
SELECT 1,3,4
UNION ALL SELECT 1,4,5
UNION ALL SELECT 1,5,12
UNION ALL SELECT 2,2,10
UNION ALL SELECT 2,6,11


--SELECT * FROM bom
/*
形成的物料清單如下:
A)
成品1
├半成品1
├半成品2
├半成品3
├原材料4

B)
成品2
├原材料2
├半成品4
├原料3

現求:

現求原材料的最頂層的半成品或者成品。
如原材料最頂層沒有半成品，則它的成品就是最頂層，如上B中"原料2"就對應“半成品2”;
如原材料的最頂層是有半品，哪麼就是半成品，如A中,"原材料4"就對應“半成品1”,B中"原料3"就對應“半成品4”

要得到的表如下:
成品名稱 原料名稱 頂層原料
----------------------------------
成品1 原材料4 半成品1
成品2 原材料3 半成品4
成品2 原材料2 成品2

*/

[simonhehe]

up

[zsforever]

--弄的比较糊涂，结果对了，帮看看
create function dbo.GetPCom(@ID int)
returns varchar(100) as
begin
declare @ancesId int
declare @OID varchar(100)
select @OID=right(@ID+1000,3)
if exists(select 1 from bom where compid=@ID)
begin
select @ancesId=ancesid from bom where compid=@ID
while exists(select 1 from bom where compid=@ID and parentid<>ancesid)
select @ID=parentid,@OID=right(parentid+1000,3)+@OID from bom where compid=@ID
set @OID=right(@ancesid+1000,3)+@OID
end
else if exists(select 1 from bom where parentid=@ID)
begin
select @OID=right(ancesid+1000,3)+@OID from bom where parentid=@ID and parentid<>ancesid
end
return @OID
end
go

select A.成品名稱,E.invname 原料名稱,case when B.顶树=C.顶树 then A.成品名稱 else D.invname end 頂層原料 from
(select A.partid,A.invid,A.invname,B.invname 成品名稱,dbo.getpcom(A.partid) 树型排列
from bas_part A,bas_part B
where left(dbo.getpcom(A.partid),3)=right(B.partid+1000,3) and
(select count(1) from bas_part where left(dbo.getpcom(partid),3)=left(dbo.getpcom(A.partid),3))>1
and len(dbo.getpcom(A.partid))=3) A,
(select B.invname 成品名稱,min(dbo.getpcom(A.partid)) 顶树
from bas_part A,bas_part B
where left(dbo.getpcom(A.partid),3)=right(B.partid+1000,3) and
(select count(1) from bas_part where left(dbo.getpcom(partid),3)=left(dbo.getpcom(A.partid),3))>1
and len(dbo.getpcom(A.partid))<>3 group by B.invname,left(dbo.getpcom(A.partid),6)) B,
(select B.invname 成品名稱,max(dbo.getpcom(A.partid)) 顶树
from bas_part A,bas_part B
where left(dbo.getpcom(A.partid),3)=right(B.partid+1000,3) and
(select count(1) from bas_part where left(dbo.getpcom(partid),3)=left(dbo.getpcom(A.partid),3))>1
and len(dbo.getpcom(A.partid))<>3 group by B.invname,left(dbo.getpcom(A.partid),6)) C,
bas_part D,bas_part E
where A.成品名稱=B.成品名稱 and A.成品名稱=C.成品名稱 and left(B.顶树,6)=left(C.顶树,6)
and right(B.顶树,3)=right(D.partid+1000,3) and right(C.顶树,3)=right(E.partid+1000,3)

[yjlhch]

路过，帮顶一下。

[谢良]

有關樹結構的東東，最好寫SP或是function.

[skyinfo]

up

[zsforever]

在BOM表中,一个物品可能是多个成品的中间物件，你这里没有，就当树型的来处理，但实际情况可能比这个要复杂,数据结构值得商榷

[marco08]

路過幫頂