有些难度的问题(BOM多级展开)

htl258_Tony 2009-02-24 01:52:00

-->1.物料表 t_IC_Item
IF OBJECT_ID ('t_IC_Item') IS NOT NULL
DROP TABLE t_IC_Item
GO

CREATE TABLE t_IC_Item
(
FItemID int, --物料内码
FNumber varchar(200),--物料代码
FName varchar(200), --物料名称
FModel varchar(200), --规格型号
FUnitID int, --基本单位内码
FErpClsID int --物料属性(1-外购 2-自制 3-委外加工)
)
GO

INSERT t_IC_Item
SELECT 1,'CP.001','产品1','产品规格1',1,2 UNION
SELECT 2,'BC.001','半成品1','半成品规格1',1,2 UNION
SELECT 3,'BC.002','半成品2','半成品规格2',1,2 UNION
SELECT 4,'BC.003','半成品3','半成品规格3',1,3 UNION
SELECT 5,'BC.004','半成品4','半成品规格4',1,2 UNION
SELECT 6,'ZC.001','主材料1','主材料规格1',1,1 UNION
SELECT 7,'ZC.002','主材料2','主材料规格2',1,1 UNION
SELECT 8,'ZC.003','主材料3','主材料规格3',1,1 UNION
SELECT 9,'ZC.004','主材料4','主材料规格4',1,1 UNION
SELECT 10,'ZC.005','主材料5','主材料规格5',1,1 UNION
SELECT 11,'ZC.006','主材料6','主材料规格6',1,1 UNION
SELECT 12,'ZC.007','主材料7','主材料规格7',1,1 UNION
SELECT 13,'ZC.008','主材料8','主材料规格8',1,1 UNION
SELECT 14,'ZC.009','主材料9','主材料规格9',1,1 UNION
SELECT 15,'ZC.010','主材料10','主材料规格10',1,1 UNION
SELECT 16,'ZC.011','主材料11','主材料规格11',1,1 UNION
SELECT 17,'ZC.012','主材料12','主材料规格12',1,1
GO

--SELECT * FROM t_IC_Item

-->2.计量单位表
IF OBJECT_ID ('t_Unit') IS NOT NULL
DROP TABLE t_Unit
GO
CREATE TABLE t_Unit
(
FID int,
FName varchar(20)--,
--FCoeffiCient int --换算率
)
GO
INSERT t_Unit SELECT 1,'个'
GO

-->3.BOM主表
IF OBJECT_ID ('t_BOM') IS NOT NULL
DROP TABLE t_BOM
GO
CREATE TABLE t_BOM
(
FID int, --自增列,主键
FBomNo varchar(200),
FItemID int,
FQty decimal(28,10),
FUnitID int
)
GO
INSERT t_BOM
SELECT 1,'BOM01',1,1,1 UNION
SELECT 2,'BOM02',2,1,1 UNION
SELECT 3,'BOM03',3,1,1 UNION
SELECT 4,'BOM04',4,1,1 UNION
SELECT 5,'BOM05',5,1,1
GO

-->4.BOM分录表
IF OBJECT_ID ('t_BOMChild') IS NOT NULL
DROP TABLE t_BOMChild
GO

CREATE TABLE t_BOMChild
(
FID int, --BOM主表的外键
FEntryID int, --行号
FItemID int,
FQty decimal(28,10),
FUnitID int
)
GO

INSERT t_BOMChild --CP.001 BOM内容
SELECT 1,1,2,1,1 UNION
SELECT 1,2,3,1,1 UNION
SELECT 1,3,15,1,1 UNION
SELECT 1,4,17,1,1

INSERT t_BOMChild --BC.001 BOM内容
SELECT 2,1,6,1,1 UNION
SELECT 2,2,7,1,1

INSERT t_BOMChild --BC.002 BOM内容
SELECT 3,1,8,1,1 UNION
SELECT 3,2,9,1,1 UNION
SELECT 3,3,7,1,1 UNION
SELECT 3,4,4,1,1

INSERT t_BOMChild --BC.003 BOM内容
SELECT 4,1,10,1,1 UNION
SELECT 4,2,11,1,1 UNION
SELECT 4,3,5,1,1

INSERT t_BOMChild --BC.004 BOM内容
SELECT 5,1,12,1,1 UNION
SELECT 5,2,13,1,1 UNION
SELECT 5,3,14,1,1 UNION
SELECT 5,4,16,1,1 UNION
SELECT 5,5,7,1,1
GO

/*
想要的结果:
序号 层次 物料代码 物料名称 物料规格 物料属性 用量 单位 BOM展开状态
1 0 CP.001 产品1 产品规格1 自制 1 个 正常
2 .1 BC.001 半成品1 半成品规格1 自制 1 个 正常
3 ..2 ZC.001 主材料1 主材料规格1 外购 1 个
4 ..2 ZC.002 主材料2 主材料规格2 外购 1 个
5 .1 BC.002 半成品2 半成品规格2 自制 1 个 正常
6 ..2 ZC.003 主材料3 主材料规格3 外购 1 个
7 ..2 ZC.004 主材料4 主材料规格4 外购 1 个
8 ..2 ZC.002 主材料2 主材料规格2 外购 1 个
9 ..2 BC.003 半成品3 半成品规格3 委外加工 1 个 正常
10 ...3 ZC.005 主材料5 主材料规格5 外购 1 个
11 ...3 ZC.006 主材料6 主材料规格6 外购 1 个
12 ...3 BC.004 半成品4 半成品规格4 自制 1 个 正常
13 ....4 ZC.007 主材料7 主材料规格7 外购 1 个
14 ....4 ZC.008 主材料8 主材料规格8 外购 1 个
15 ....4 ZC.009 主材料9 主材料规格9 外购 1 个
16 ....4 ZC.011 主材料11 主材料规格11 外购 1 个
17 ....4 ZC.002 主材料2 主材料规格2 外购 1 个
18 .1 ZC.010 主材料10 主材料规格10 外购 1 个
19 .1 ZC.012 主材料12 主材料规格12 外购 1 个
*/


说明:以上分别为各相关表结构和一些测试数据,现想建一函数或存储过程(传入内容为产品代码或半成品代码),通过物料表的物料属性(外购,自制,委外加工),当t_BOMChild 表内对应的FItemID的物料属性为"自制"或"委外加工"的时候,自动展开该物料BOM至最底层,并且记录该展开状态("正常"或"BOM未建"),请各路DX帮助!
...全文
461 21 打赏 收藏 转发到动态 举报
写回复
用AI写文章
21 条回复
切换为时间正序
请发表友善的回复…
发表回复
子陌红尘 2009-02-25
  • 打赏
  • 举报
回复
SQL Server 2000的:


CREATE TABLE t_IC_Item(FItemID int,FNumber varchar(200),FName varchar(200),FModel varchar(200),FUnitID int,FErpClsID int)

INSERT t_IC_Item
SELECT 1,'CP.001','产品1' ,'产品规格1' ,1,2 UNION
SELECT 2,'BC.001','半成品1' ,'半成品规格1' ,1,2 UNION
SELECT 3,'BC.002','半成品2' ,'半成品规格2' ,1,2 UNION
SELECT 4,'BC.003','半成品3' ,'半成品规格3' ,1,3 UNION
SELECT 5,'BC.004','半成品4' ,'半成品规格4' ,1,2 UNION
SELECT 6,'ZC.001','主材料1' ,'主材料规格1' ,1,1 UNION
SELECT 7,'ZC.002','主材料2' ,'主材料规格2' ,1,1 UNION
SELECT 8,'ZC.003','主材料3' ,'主材料规格3' ,1,1 UNION
SELECT 9,'ZC.004','主材料4' ,'主材料规格4' ,1,1 UNION
SELECT 10,'ZC.005','主材料5' ,'主材料规格5' ,1,1 UNION
SELECT 11,'ZC.006','主材料6' ,'主材料规格6' ,1,1 UNION
SELECT 12,'ZC.007','主材料7' ,'主材料规格7' ,1,1 UNION
SELECT 13,'ZC.008','主材料8' ,'主材料规格8' ,1,1 UNION
SELECT 14,'ZC.009','主材料9' ,'主材料规格9' ,1,1 UNION
SELECT 15,'ZC.010','主材料10','主材料规格10',1,1 UNION
SELECT 16,'ZC.011','主材料11','主材料规格11',1,1 UNION
SELECT 17,'ZC.012','主材料12','主材料规格12',1,1

CREATE TABLE t_Unit(FID int,FName varchar(20))
INSERT t_Unit SELECT 1,'个'

CREATE TABLE t_BOM(FID int,FBomNo varchar(200),FItemID int,FQty decimal(28,10),FUnitID int)
INSERT t_BOM
SELECT 1,'BOM01',1,1,1 UNION
SELECT 2,'BOM02',2,1,1 UNION
SELECT 3,'BOM03',3,1,1 UNION
SELECT 4,'BOM04',4,1,1 UNION
SELECT 5,'BOM05',5,1,1

CREATE TABLE t_BOMChild(FID int,FEntryID int,FItemID int,FQty decimal(28,10),FUnitID int)
INSERT t_BOMChild
SELECT 1,1, 2,1,1 UNION
SELECT 1,2, 3,1,1 UNION
SELECT 1,3,15,1,1 UNION
SELECT 1,4,17,1,1 UNION
SELECT 2,1, 6,1,1 UNION
SELECT 2,2, 7,1,1 UNION
SELECT 3,1, 8,1,1 UNION
SELECT 3,2, 9,1,1 UNION
SELECT 3,3, 7,1,1 UNION
SELECT 3,4, 4,1,1 UNION
SELECT 4,1,10,1,1 UNION
SELECT 4,2,11,1,1 UNION
SELECT 4,3, 5,1,1 UNION
SELECT 5,1,12,1,1 UNION
SELECT 5,2,13,1,1 UNION
SELECT 5,3,14,1,1 UNION
SELECT 5,4,16,1,1 UNION
SELECT 5,5, 7,1,1
GO

create procedure sp_test
as
begin
declare @i int
declare @t table(Level int,Code varchar(40),FItemID int,FNumber varchar(20),FName varchar(20),
FModel varchar(20),FErpClsID int,FQty int,FName1 varchar(20),Status varchar(20))

declare @t1 table(id int identity(1,1),Level varchar(10),Code varchar(40),FItemID int,FNumber varchar(20),FName varchar(20),
FModel varchar(20),FErpClsID varchar(20),FQty int,FName1 varchar(20),Status varchar(20))

set @i=0

insert into @t
select
distinct @i,right('000'+rtrim(a.FItemID),4),a.FItemID,a.FNumber,a.FName,a.FModel,a.FErpClsID,b.FQty,c.FName,'正常'
from
t_IC_Item a,t_BOM b,t_Unit c
where
a.FUnitID=b.FUnitID and b.FUnitID=c.FID
and
not exists(select 1 from t_BOM d,t_BOMChild e where d.FID=e.FID and e.FItemID=a.FItemID)

while @@rowcount>0
begin
set @i=@i+1

insert into @t
select
distinct @i,e.Code+right('000'+rtrim(d.FEntryID),4),
a.FItemID,a.FNumber,a.FName,a.FModel,a.FErpClsID,b.FQty*d.FQty*e.FQty,c.FName,
case when a.FItemID in(select FID from t_BOM) then '正常' else '' end
from
t_IC_Item a,t_BOM b,t_Unit c,t_BOMChild d,@t e
where
a.FUnitID=b.FUnitID
and b.FUnitID=c.FID
and a.FItemID=d.FItemID
and d.FID=e.FItemID
and e.level=@i-1
end

insert into @t1(Level,Code ,FItemID ,FNumber,FName,FModel,FErpClsID,FQty,FName1,Status)
select
t.*
from
(select top 100 percent
REPLICATE('.',Level)+rtrim(Level) as level,
Code,FItemID,FNumber,FName,
FModel,(case FErpClsID when 1 then '外购' when 2 then '自制'when 3 then '委外加工' else '' end) as FErpClsID,
FQty,FName1,Status
from
@t
order by
code) t

select Id,Level ,FItemID,FName,FModel,FErpClsID,FQty,FName1,Status from @t1 order by code
end
go

exec sp_test
go


/*
Id Level FItemID FName FModel FErpClsID FQty FName1 Status
----------- ---------- ----------- -------------------- -------------------- -------------------- ----------- -------------------- --------------------
1 0 1 产品1 产品规格1 自制 1 个 正常
2 .1 2 半成品1 半成品规格1 自制 1 个 正常
3 ..2 6 主材料1 主材料规格1 外购 1 个
4 ..2 7 主材料2 主材料规格2 外购 1 个
5 .1 3 半成品2 半成品规格2 自制 1 个 正常
6 ..2 8 主材料3 主材料规格3 外购 1 个
7 ..2 9 主材料4 主材料规格4 外购 1 个
8 ..2 7 主材料2 主材料规格2 外购 1 个
9 ..2 4 半成品3 半成品规格3 委外加工 1 个 正常
10 ...3 10 主材料5 主材料规格5 外购 1 个
11 ...3 11 主材料6 主材料规格6 外购 1 个
12 ...3 5 半成品4 半成品规格4 自制 1 个 正常
13 ....4 12 主材料7 主材料规格7 外购 1 个
14 ....4 13 主材料8 主材料规格8 外购 1 个
15 ....4 14 主材料9 主材料规格9 外购 1 个
16 ....4 16 主材料11 主材料规格11 外购 1 个
17 ....4 7 主材料2 主材料规格2 外购 1 个
18 .1 15 主材料10 主材料规格10 外购 1 个
19 .1 17 主材料12 主材料规格12 外购 1 个
*/

DROP procedure sp_test
DROP TABLE t_IC_Item
DROP TABLE t_Unit
DROP TABLE t_BOM
DROP TABLE t_BOMChild
GO
htl258_Tony 2009-02-25
  • 打赏
  • 举报
回复
-->1.物料表 t_IC_Item
IF OBJECT_ID ('t_IC_Item') IS NOT NULL
DROP TABLE t_IC_Item
GO

CREATE TABLE t_IC_Item
(
FItemID int, --物料内码
FNumber varchar(200),--物料代码
FName varchar(200), --物料名称
FModel varchar(200), --规格型号
FUnitID int, --基本单位内码
FErpClsID int --物料属性(1-外购 2-自制 3-委外加工)
)
GO

INSERT t_IC_Item
SELECT 1,'CP.001','产品1','产品规格1',1,2 UNION
SELECT 2,'BC.001','半成品1','半成品规格1',1,2 UNION
SELECT 3,'BC.002','半成品2','半成品规格2',1,2 UNION
SELECT 4,'BC.003','半成品3','半成品规格3',1,3 UNION
SELECT 5,'BC.004','半成品4','半成品规格4',1,2 UNION
SELECT 6,'ZC.001','主材料1','主材料规格1',1,1 UNION
SELECT 7,'ZC.002','主材料2','主材料规格2',1,1 UNION
SELECT 8,'ZC.003','主材料3','主材料规格3',1,1 UNION
SELECT 9,'ZC.004','主材料4','主材料规格4',1,1 UNION
SELECT 10,'ZC.005','主材料5','主材料规格5',1,1 UNION
SELECT 11,'ZC.006','主材料6','主材料规格6',1,1 UNION
SELECT 12,'ZC.007','主材料7','主材料规格7',1,1 UNION
SELECT 13,'ZC.008','主材料8','主材料规格8',1,1 UNION
SELECT 14,'ZC.009','主材料9','主材料规格9',1,1 UNION
SELECT 15,'ZC.010','主材料10','主材料规格10',1,1 UNION
SELECT 16,'ZC.011','主材料11','主材料规格11',1,1 UNION
SELECT 17,'ZC.012','主材料12','主材料规格12',1,1
GO

--SELECT * FROM t_IC_Item

-->2.计量单位表
IF OBJECT_ID ('t_Unit') IS NOT NULL
DROP TABLE t_Unit
GO
CREATE TABLE t_Unit
(
FID int,
FName varchar(20)--,
--FCoeffiCient int --换算率
)
GO
INSERT t_Unit SELECT 1,'个'
GO

-->3.BOM主表
IF OBJECT_ID ('t_BOM') IS NOT NULL
DROP TABLE t_BOM
GO
CREATE TABLE t_BOM
(
FID int, --自增列,主键
FBomNo varchar(200),
FItemID int,
FQty decimal(28,10),
FUnitID int
)
GO
INSERT t_BOM
SELECT 1,'BOM01',1,1,1 UNION
SELECT 2,'BOM02',2,1,1 UNION
SELECT 3,'BOM03',3,1,1 UNION
SELECT 4,'BOM04',4,1,1 --UNION
--SELECT 5,'BOM05',5,1,1
GO

-->4.BOM分录表
IF OBJECT_ID ('t_BOMChild') IS NOT NULL
DROP TABLE t_BOMChild
GO

CREATE TABLE t_BOMChild
(
FID int, --BOM主表的外键
FEntryID int, --行号
FItemID int,
FQty decimal(28,10),
FUnitID int
)
GO

INSERT t_BOMChild --CP.001 BOM内容
SELECT 1,1,2,1,1 UNION
SELECT 1,2,3,1,1 UNION
SELECT 1,3,15,1,1 UNION
SELECT 1,4,17,1,1

INSERT t_BOMChild --BC.001 BOM内容
SELECT 2,1,6,1,1 UNION
SELECT 2,2,7,1,1

INSERT t_BOMChild --BC.002 BOM内容
SELECT 3,1,8,1,1 UNION
SELECT 3,2,9,1,1 UNION
SELECT 3,3,7,1,1 UNION
SELECT 3,4,4,1,1

INSERT t_BOMChild --BC.003 BOM内容
SELECT 4,1,10,1,1 UNION
SELECT 4,2,11,1,1 UNION
SELECT 4,3,5,1,1

/*INSERT t_BOMChild --BC.004 BOM内容
SELECT 5,1,12,1,1 UNION
SELECT 5,2,13,1,1 UNION
SELECT 5,3,14,1,1 UNION
SELECT 5,4,16,1,1 UNION
SELECT 5,5,7,1,1
GO
*/
GO

我将最后一张BOM去掉,显示不出“BOM未建”,这个过程要怎么处理呢?
子陌红尘 2009-02-25
  • 打赏
  • 举报
回复

CREATE TABLE t_IC_Item(FItemID int,FNumber varchar(200),FName varchar(200),FModel varchar(200),FUnitID int,FErpClsID int)

INSERT t_IC_Item
SELECT 1,'CP.001','产品1' ,'产品规格1' ,1,2 UNION
SELECT 2,'BC.001','半成品1' ,'半成品规格1' ,1,2 UNION
SELECT 3,'BC.002','半成品2' ,'半成品规格2' ,1,2 UNION
SELECT 4,'BC.003','半成品3' ,'半成品规格3' ,1,3 UNION
SELECT 5,'BC.004','半成品4' ,'半成品规格4' ,1,2 UNION
SELECT 6,'ZC.001','主材料1' ,'主材料规格1' ,1,1 UNION
SELECT 7,'ZC.002','主材料2' ,'主材料规格2' ,1,1 UNION
SELECT 8,'ZC.003','主材料3' ,'主材料规格3' ,1,1 UNION
SELECT 9,'ZC.004','主材料4' ,'主材料规格4' ,1,1 UNION
SELECT 10,'ZC.005','主材料5' ,'主材料规格5' ,1,1 UNION
SELECT 11,'ZC.006','主材料6' ,'主材料规格6' ,1,1 UNION
SELECT 12,'ZC.007','主材料7' ,'主材料规格7' ,1,1 UNION
SELECT 13,'ZC.008','主材料8' ,'主材料规格8' ,1,1 UNION
SELECT 14,'ZC.009','主材料9' ,'主材料规格9' ,1,1 UNION
SELECT 15,'ZC.010','主材料10','主材料规格10',1,1 UNION
SELECT 16,'ZC.011','主材料11','主材料规格11',1,1 UNION
SELECT 17,'ZC.012','主材料12','主材料规格12',1,1

CREATE TABLE t_Unit(FID int,FName varchar(20))
INSERT t_Unit SELECT 1,'个'

CREATE TABLE t_BOM(FID int,FBomNo varchar(200),FItemID int,FQty decimal(28,10),FUnitID int)
INSERT t_BOM
SELECT 1,'BOM01',1,1,1 UNION
SELECT 2,'BOM02',2,1,1 UNION
SELECT 3,'BOM03',3,1,1 UNION
SELECT 4,'BOM04',4,1,1 UNION
SELECT 5,'BOM05',5,1,1

CREATE TABLE t_BOMChild(FID int,FEntryID int,FItemID int,FQty decimal(28,10),FUnitID int)
INSERT t_BOMChild
SELECT 1,1, 2,1,1 UNION
SELECT 1,2, 3,1,1 UNION
SELECT 1,3,15,1,1 UNION
SELECT 1,4,17,1,1 UNION
SELECT 2,1, 6,1,1 UNION
SELECT 2,2, 7,1,1 UNION
SELECT 3,1, 8,1,1 UNION
SELECT 3,2, 9,1,1 UNION
SELECT 3,3, 7,1,1 UNION
SELECT 3,4, 4,1,1 UNION
SELECT 4,1,10,1,1 UNION
SELECT 4,2,11,1,1 UNION
SELECT 4,3, 5,1,1 /*UNION
SELECT 5,1,12,1,1 UNION
SELECT 5,2,13,1,1 UNION
SELECT 5,3,14,1,1 UNION
SELECT 5,4,16,1,1 UNION
SELECT 5,5, 7,1,1*/
GO

create procedure sp_test
as
begin
declare @i int
declare @t table(Level int,Code varchar(40),FItemID int,FNumber varchar(20),FName varchar(20),
FModel varchar(20),FErpClsID int,FQty int,FName1 varchar(20),Status varchar(20))

declare @t1 table(id int identity(1,1),Level varchar(10),Code varchar(40),FItemID int,FNumber varchar(20),FName varchar(20),
FModel varchar(20),FErpClsID varchar(20),FQty int,FName1 varchar(20),Status varchar(20))

set @i=0

insert into @t
select
distinct @i,right('000'+rtrim(a.FItemID),4),a.FItemID,a.FNumber,a.FName,a.FModel,a.FErpClsID,b.FQty,c.FName,'正常'
from
t_IC_Item a,t_BOM b,t_Unit c
where
a.FUnitID=b.FUnitID and b.FUnitID=c.FID
and
not exists(select 1 from t_BOM d,t_BOMChild e where d.FID=e.FID and e.FItemID=a.FItemID)
and
exists(select 1 from t_BOM m,t_BOMChild n where m.FID=n.FID and M.FID=a.FItemID)

while @@rowcount>0
begin
set @i=@i+1

insert into @t
select
@i,e.Code+right('000'+rtrim(d.FEntryID),4),
a.FItemID,a.FNumber,a.FName,a.FModel,a.FErpClsID,b.FQty*d.FQty*e.FQty,c.FName,
case when a.FItemID in(select m.FID from t_BOM m,t_BOMChild n where m.FID=n.FID) then '正常'
when a.FItemID in(select FID from t_BOM) then 'BOM未建'
else ''
end
from
t_IC_Item a,t_BOM b,t_Unit c,t_BOMChild d,@t e
where
a.FUnitID=b.FUnitID
and b.FUnitID=c.FID
and a.FItemID=d.FItemID
and b.FID=d.FID
and d.FID=e.FItemID
and e.level=@i-1
end

insert into @t1(Level,Code ,FItemID ,FNumber,FName,FModel,FErpClsID,FQty,FName1,Status)
select
t.*
from
(select top 100 percent
REPLICATE('.',Level)+rtrim(Level) as level,
Code,FItemID,FNumber,FName,
FModel,(case FErpClsID when 1 then '外购' when 2 then '自制'when 3 then '委外加工' else '' end) as FErpClsID,
FQty,FName1,Status
from
@t
order by
code) t

select Id,Level ,FItemID,FName,FModel,FErpClsID,FQty,FName1,Status from @t1 order by code
end
go

exec sp_test
/*
Id Level FItemID FName FModel FErpClsID FQty FName1 Status
----------- ---------- ----------- -------------------- -------------------- -------------------- ----------- -------------------- --------------------
1 0 1 产品1 产品规格1 自制 1 个 正常
2 .1 2 半成品1 半成品规格1 自制 1 个 正常
3 ..2 6 主材料1 主材料规格1 外购 1 个
4 ..2 7 主材料2 主材料规格2 外购 1 个
5 .1 3 半成品2 半成品规格2 自制 1 个 正常
6 ..2 8 主材料3 主材料规格3 外购 1 个
7 ..2 9 主材料4 主材料规格4 外购 1 个
8 ..2 7 主材料2 主材料规格2 外购 1 个
9 ..2 4 半成品3 半成品规格3 委外加工 1 个 正常
10 ...3 10 主材料5 主材料规格5 外购 1 个
11 ...3 11 主材料6 主材料规格6 外购 1 个
12 ...3 5 半成品4 半成品规格4 自制 1 个 BOM未建
13 .1 15 主材料10 主材料规格10 外购 1 个
14 .1 17 主材料12 主材料规格12 外购 1 个
*/
go


DROP procedure sp_test
DROP TABLE t_IC_Item
DROP TABLE t_Unit
DROP TABLE t_BOM
DROP TABLE t_BOMChild
GO
htl258_Tony 2009-02-24
  • 打赏
  • 举报
回复
-->1.物料表 t_IC_Item
IF OBJECT_ID ('t_IC_Item') IS NOT NULL
DROP TABLE t_IC_Item
GO

CREATE TABLE t_IC_Item
(
FItemID int, --物料内码
FNumber varchar(200),--物料代码
FName varchar(200), --物料名称
FModel varchar(200), --规格型号
FUnitID int, --基本单位内码
FErpClsID int --物料属性(1-外购 2-自制 3-委外加工)
)
GO

INSERT t_IC_Item
SELECT 1,'CP.001','产品1','产品规格1',1,2 UNION
SELECT 2,'BC.001','半成品1','半成品规格1',1,2 UNION
SELECT 3,'BC.002','半成品2','半成品规格2',1,2 UNION
SELECT 4,'BC.003','半成品3','半成品规格3',1,3 UNION
SELECT 5,'BC.004','半成品4','半成品规格4',1,2 UNION
SELECT 6,'ZC.001','主材料1','主材料规格1',1,1 UNION
SELECT 7,'ZC.002','主材料2','主材料规格2',1,1 UNION
SELECT 8,'ZC.003','主材料3','主材料规格3',1,1 UNION
SELECT 9,'ZC.004','主材料4','主材料规格4',1,1 UNION
SELECT 10,'ZC.005','主材料5','主材料规格5',1,1 UNION
SELECT 11,'ZC.006','主材料6','主材料规格6',1,1 UNION
SELECT 12,'ZC.007','主材料7','主材料规格7',1,1 UNION
SELECT 13,'ZC.008','主材料8','主材料规格8',1,1 UNION
SELECT 14,'ZC.009','主材料9','主材料规格9',1,1 UNION
SELECT 15,'ZC.010','主材料10','主材料规格10',1,1 UNION
SELECT 16,'ZC.011','主材料11','主材料规格11',1,1 UNION
SELECT 17,'ZC.012','主材料12','主材料规格12',1,1
GO

--SELECT * FROM t_IC_Item

-->2.计量单位表
IF OBJECT_ID ('t_Unit') IS NOT NULL
DROP TABLE t_Unit
GO
CREATE TABLE t_Unit
(
FID int,
FName varchar(20)--,
--FCoeffiCient int --换算率
)
GO
INSERT t_Unit SELECT 1,'个'
GO

-->3.BOM主表
IF OBJECT_ID ('t_BOM') IS NOT NULL
DROP TABLE t_BOM
GO
CREATE TABLE t_BOM
(
FID int, --自增列,主键
FBomNo varchar(200),
FItemID int,
FQty decimal(28,10),
FUnitID int
)
GO
INSERT t_BOM
SELECT 1,'BOM01',1,1,1 UNION
SELECT 2,'BOM02',2,1,1 UNION
SELECT 3,'BOM03',3,1,1 UNION
SELECT 4,'BOM04',4,1,1 --UNION
--SELECT 5,'BOM05',5,1,1
GO

-->4.BOM分录表
IF OBJECT_ID ('t_BOMChild') IS NOT NULL
DROP TABLE t_BOMChild
GO

CREATE TABLE t_BOMChild
(
FID int, --BOM主表的外键
FEntryID int, --行号
FItemID int,
FQty decimal(28,10),
FUnitID int
)
GO

INSERT t_BOMChild --CP.001 BOM内容
SELECT 1,1,2,1,1 UNION
SELECT 1,2,3,1,1 UNION
SELECT 1,3,15,1,1 UNION
SELECT 1,4,17,1,1

INSERT t_BOMChild --BC.001 BOM内容
SELECT 2,1,6,1,1 UNION
SELECT 2,2,7,1,1

INSERT t_BOMChild --BC.002 BOM内容
SELECT 3,1,8,1,1 UNION
SELECT 3,2,9,1,1 UNION
SELECT 3,3,7,1,1 UNION
SELECT 3,4,4,1,1

INSERT t_BOMChild --BC.003 BOM内容
SELECT 4,1,10,1,1 UNION
SELECT 4,2,11,1,1 UNION
SELECT 4,3,5,1,1

/*INSERT t_BOMChild --BC.004 BOM内容
SELECT 5,1,12,1,1 UNION
SELECT 5,2,13,1,1 UNION
SELECT 5,3,14,1,1 UNION
SELECT 5,4,16,1,1 UNION
SELECT 5,5,7,1,1
GO
*/
GO
create procedure p1
@ID int
as
begin
; with BOM as
(select *, FQty=1, [Level]=0, status=0, Ord=cast(FName as varchar(1000))
from t_IC_Item where FItemID=@ID
union all
select I.*, FQty=1, [Level]=B.[Level]+1,status=B.FItemID
, Ord=cast(B.Ord+rtrim(C.FEntryID)+I.FName as varchar(1000))
from t_IC_Item I join t_BOMChild C on I.FItemID=C.FItemID
join BOM B on C.FID=B.FItemID
)
select 序号=Row_Number() over(order by Ord)
, 层次=replicate('.',[Level])+rtrim([Level])
, 物料代码=FNumber
, 物料名称=B.FName
, 物料规格=FModel
, 物料属性=case FErpClsID when 1 then '外购' when 2 then '自制'
when 3 then '委外加工' else '' end
, 用量=FQty
, 单位=U.FName
, BOM展开状态=case when FItemID in (select status from BOM) then '正常' else '' end
from BOM B join t_Unit U on B.FUnitID=U.FID
end

GO

exec P1 1

drop procedure P1
drop table t_IC_Item, t_Unit, t_BOM, t_BOMChild

像这样,我将最后一张BOM去掉,显示不出“BOM未建”,这段代码还得改一下
htl258_Tony 2009-02-24
  • 打赏
  • 举报
回复
再顶一下,期待SQL2000的解答
kye_jufei 2009-02-24
  • 打赏
  • 举报
回复
主要在於產生BOM架構時的速度
sql遞歸可以實現,用delphi語句也可以實現,類似於windows的資源管理器
htl258_Tony 2009-02-24
  • 打赏
  • 举报
回复
转成SQL2000呢
dobear_0922 2009-02-24
  • 打赏
  • 举报
回复
不清楚你的用量是怎么算的,直接写了个FQty=1,
dobear_0922 2009-02-24
  • 打赏
  • 举报
回复
结果:
/*
序号 层次 物料代码 物料名称 物料规格 物料属性 用量 单位 BOM展开状态
-------------------- ---------------- ---------------- ---------------- ---------------- -------- ----------- -------------------- -------
1 0 CP.001 产品1 产品规格1 自制 1 个 正常
2 .1 BC.001 半成品1 半成品规格1 自制 1 个 正常
3 ..2 ZC.001 主材料1 主材料规格1 外购 1 个
4 ..2 ZC.002 主材料2 主材料规格2 外购 1 个
5 .1 BC.002 半成品2 半成品规格2 自制 1 个 正常
6 ..2 ZC.003 主材料3 主材料规格3 外购 1 个
7 ..2 ZC.004 主材料4 主材料规格4 外购 1 个
8 ..2 ZC.002 主材料2 主材料规格2 外购 1 个
9 ..2 BC.003 半成品3 半成品规格3 委外加工 1 个 正常
10 ...3 ZC.005 主材料5 主材料规格5 外购 1 个
11 ...3 ZC.006 主材料6 主材料规格6 外购 1 个
12 ...3 BC.004 半成品4 半成品规格4 自制 1 个 正常
13 ....4 ZC.007 主材料7 主材料规格7 外购 1 个
14 ....4 ZC.008 主材料8 主材料规格8 外购 1 个
15 ....4 ZC.009 主材料9 主材料规格9 外购 1 个
16 ....4 ZC.011 主材料11 主材料规格11 外购 1 个
17 ....4 ZC.002 主材料2 主材料规格2 外购 1 个
18 .1 ZC.010 主材料10 主材料规格10 外购 1 个
19 .1 ZC.012 主材料12 主材料规格12 外购 1 个

(19 行受影响)

*/
dobear_0922 2009-02-24
  • 打赏
  • 举报
回复
-->1.物料表 t_IC_Item
IF OBJECT_ID ('t_IC_Item') IS NOT NULL
DROP TABLE t_IC_Item
GO

CREATE TABLE t_IC_Item
(
FItemID int, --物料内码
FNumber varchar(200),--物料代码
FName varchar(200), --物料名称
FModel varchar(200), --规格型号
FUnitID int, --基本单位内码
FErpClsID int --物料属性(1-外购 2-自制 3-委外加工)
)
GO

INSERT t_IC_Item
SELECT 1,'CP.001','产品1','产品规格1',1,2 UNION
SELECT 2,'BC.001','半成品1','半成品规格1',1,2 UNION
SELECT 3,'BC.002','半成品2','半成品规格2',1,2 UNION
SELECT 4,'BC.003','半成品3','半成品规格3',1,3 UNION
SELECT 5,'BC.004','半成品4','半成品规格4',1,2 UNION
SELECT 6,'ZC.001','主材料1','主材料规格1',1,1 UNION
SELECT 7,'ZC.002','主材料2','主材料规格2',1,1 UNION
SELECT 8,'ZC.003','主材料3','主材料规格3',1,1 UNION
SELECT 9,'ZC.004','主材料4','主材料规格4',1,1 UNION
SELECT 10,'ZC.005','主材料5','主材料规格5',1,1 UNION
SELECT 11,'ZC.006','主材料6','主材料规格6',1,1 UNION
SELECT 12,'ZC.007','主材料7','主材料规格7',1,1 UNION
SELECT 13,'ZC.008','主材料8','主材料规格8',1,1 UNION
SELECT 14,'ZC.009','主材料9','主材料规格9',1,1 UNION
SELECT 15,'ZC.010','主材料10','主材料规格10',1,1 UNION
SELECT 16,'ZC.011','主材料11','主材料规格11',1,1 UNION
SELECT 17,'ZC.012','主材料12','主材料规格12',1,1
GO

--SELECT * FROM t_IC_Item

-->2.计量单位表
IF OBJECT_ID ('t_Unit') IS NOT NULL
DROP TABLE t_Unit
GO
CREATE TABLE t_Unit
(
FID int,
FName varchar(20)--,
--FCoeffiCient int --换算率
)
GO
INSERT t_Unit SELECT 1,'个'
GO

-->3.BOM主表
IF OBJECT_ID ('t_BOM') IS NOT NULL
DROP TABLE t_BOM
GO
CREATE TABLE t_BOM
(
FID int, --自增列,主键
FBomNo varchar(200),
FItemID int,
FQty decimal(28,10),
FUnitID int
)
GO
INSERT t_BOM
SELECT 1,'BOM01',1,1,1 UNION
SELECT 2,'BOM02',2,1,1 UNION
SELECT 3,'BOM03',3,1,1 UNION
SELECT 4,'BOM04',4,1,1 UNION
SELECT 5,'BOM05',5,1,1
GO

-->4.BOM分录表
IF OBJECT_ID ('t_BOMChild') IS NOT NULL
DROP TABLE t_BOMChild
GO

CREATE TABLE t_BOMChild
(
FID int, --BOM主表的外键
FEntryID int, --行号
FItemID int,
FQty decimal(28,10),
FUnitID int
)
GO

INSERT t_BOMChild --CP.001 BOM内容
SELECT 1,1,2,1,1 UNION
SELECT 1,2,3,1,1 UNION
SELECT 1,3,15,1,1 UNION
SELECT 1,4,17,1,1

INSERT t_BOMChild --BC.001 BOM内容
SELECT 2,1,6,1,1 UNION
SELECT 2,2,7,1,1

INSERT t_BOMChild --BC.002 BOM内容
SELECT 3,1,8,1,1 UNION
SELECT 3,2,9,1,1 UNION
SELECT 3,3,7,1,1 UNION
SELECT 3,4,4,1,1

INSERT t_BOMChild --BC.003 BOM内容
SELECT 4,1,10,1,1 UNION
SELECT 4,2,11,1,1 UNION
SELECT 4,3,5,1,1

INSERT t_BOMChild --BC.004 BOM内容
SELECT 5,1,12,1,1 UNION
SELECT 5,2,13,1,1 UNION
SELECT 5,3,14,1,1 UNION
SELECT 5,4,16,1,1 UNION
SELECT 5,5,7,1,1
GO

create procedure p1
@ID int
as
begin
; with BOM as
(select *, FQty=1, [Level]=0, status=0, Ord=cast(FName as varchar(1000))
from t_IC_Item where FItemID=@ID
union all
select I.*, FQty=1, [Level]=B.[Level]+1,status=B.FItemID
, Ord=cast(B.Ord+rtrim(C.FEntryID)+I.FName as varchar(1000))
from t_IC_Item I join t_BOMChild C on I.FItemID=C.FItemID
join BOM B on C.FID=B.FItemID
)
select 序号=Row_Number() over(order by Ord)
, 层次=replicate('.',[Level])+rtrim([Level])
, 物料代码=FNumber
, 物料名称=B.FName
, 物料规格=FModel
, 物料属性=case FErpClsID when 1 then '外购' when 2 then '自制'
when 3 then '委外加工' else '' end
, 用量=FQty
, 单位=U.FName
, BOM展开状态=case when FItemID in (select status from BOM) then '正常' else '' end
from BOM B join t_Unit U on B.FUnitID=U.FID
end
go


exec P1 1

drop procedure P1
drop table t_IC_Item, t_Unit, t_BOM, t_BOMChild
dawugui 2009-02-24
  • 打赏
  • 举报
回复
这么多表,也不说明一下,晚上有时间再帮你做.
dawugui 2009-02-24
  • 打赏
  • 举报
回复
看看.
htl258_Tony 2009-02-24
  • 打赏
  • 举报
回复
这边也快沉下去了,再顶一下
htl258_Tony 2009-02-24
  • 打赏
  • 举报
回复
大虾快出手吧
htl258_Tony 2009-02-24
  • 打赏
  • 举报
回复
先2000再2005吧,准备考虑升级SQL
dobear_0922 2009-02-24
  • 打赏
  • 举报
回复
[Quote=引用 1 楼 htl258 的回复:]
语句越简练越好
[/Quote]
SQL2005可以递归查询,SQL2000得写函数
dobear_0922 2009-02-24
  • 打赏
  • 举报
回复
SQL2000还是SQL2005?
claro 2009-02-24
  • 打赏
  • 举报
回复
帮顶。
肥龙上天 2009-02-24
  • 打赏
  • 举报
回复
好长!
htl258_Tony 2009-02-24
  • 打赏
  • 举报
回复
语句越简练越好

22,206

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧