有些难度的问题(BOM多级展开)
-->1.物料表 t_IC_ItemIF OBJECT_ID ('t_IC_Item') IS NOT NULL DROP TABLE t_IC_ItemGOCREATE TABLE t_IC_Item( FItemID int, --物料内码 FNumber varchar(200),--物料代码 FName varchar(200), --物料名称 FModel varchar(200), --规格型号 FUnitID int, --基本单位内码 FErpClsID int --物料属性(1-外购 2-自制 3-委外加工))GOINSERT 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_UnitGOCREATE TABLE t_Unit( FID int, FName varchar(20)--, --FCoeffiCient int --换算率)GOINSERT t_Unit SELECT 1,'个'GO-->3.BOM主表IF OBJECT_ID ('t_BOM') IS NOT NULL DROP TABLE t_BOMGOCREATE TABLE t_BOM ( FID int, --自增列，主键 FBomNo varchar(200), FItemID int, FQty decimal(28,10), FUnitID int)GOINSERT 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_BOMChildGOCREATE TABLE t_BOMChild ( FID int, --BOM主表的外键 FEntryID int, --行号 FItemID int, FQty decimal(28,10), FUnitID int)GOINSERT 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,1INSERT 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,1GO/*想要的结果:序号 层次 物料代码 物料名称 物料规格 物料属性 用量 单位 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 个*/
[解决办法]
好长！
[解决办法]
帮顶。
[解决办法]
SQL2000还是SQL2005？
[解决办法]
看看.
[解决办法]
-->1.物料表 t_IC_ItemIF OBJECT_ID ('t_IC_Item') IS NOT NULL DROP TABLE t_IC_ItemGOCREATE TABLE t_IC_Item( FItemID int, --物料内码 FNumber varchar(200),--物料代码 FName varchar(200), --物料名称 FModel
[解决办法]
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_ItemSELECT 1,'CP.001','产品1' ,'产品规格1' ,1,2 UNIONSELECT 2,'BC.001','半成品1' ,'半成品规格1' ,1,2 UNIONSELECT 3,'BC.002','半成品2' ,'半成品规格2' ,1,2 UNIONSELECT 4,'BC.003','半成品3' ,'半成品规格3' ,1,3 UNIONSELECT 5,'BC.004','半成品4' ,'半成品规格4' ,1,2 UNIONSELECT 6,'ZC.001','主材料1' ,'主材料规格1' ,1,1 UNIONSELECT 7,'ZC.002','主材料2' ,'主材料规格2' ,1,1 UNIONSELECT 8,'ZC.003','主材料3' ,'主材料规格3' ,1,1 UNIONSELECT 9,'ZC.004','主材料4' ,'主材料规格4' ,1,1 UNIONSELECT 10,'ZC.005','主材料5' ,'主材料规格5' ,1,1 UNIONSELECT 11,'ZC.006','主材料6' ,'主材料规格6' ,1,1 UNIONSELECT 12,'ZC.007','主材料7' ,'主材料规格7' ,1,1 UNIONSELECT 13,'ZC.008','主材料8' ,'主材料规格8' ,1,1 UNIONSELECT 14,'ZC.009','主材料9' ,'主材料规格9' ,1,1 UNIONSELECT 15,'ZC.010','主材料10','主材料规格10',1,1 UNIONSELECT 16,'ZC.011','主材料11','主材料规格11',1,1 UNIONSELECT 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 UNIONSELECT 2,'BOM02',2,1,1 UNIONSELECT 3,'BOM03',3,1,1 UNIONSELECT 4,'BOM04',4,1,1 UNIONSELECT 5,'BOM05',5,1,1 CREATE TABLE t_BOMChild(FID int,FEntryID int,FItemID int,FQty decimal(28,10),FUnitID int)INSERT t_BOMChildSELECT 1,1, 2,1,1 UNIONSELECT 1,2, 3,1,1 UNIONSELECT 1,3,15,1,1 UNIONSELECT 1,4,17,1,1 UNIONSELECT 2,1, 6,1,1 UNIONSELECT 2,2, 7,1,1 UNIONSELECT 3,1, 8,1,1 UNIONSELECT 3,2, 9,1,1 UNIONSELECT 3,3, 7,1,1 UNIONSELECT 3,4, 4,1,1 UNIONSELECT 4,1,10,1,1 UNIONSELECT 4,2,11,1,1 UNIONSELECT 4,3, 5,1,1 UNIONSELECT 5,1,12,1,1 UNIONSELECT 5,2,13,1,1 UNIONSELECT 5,3,14,1,1 UNIONSELECT 5,4,16,1,1 UNIONSELECT 5,5, 7,1,1GOcreate procedure sp_testasbegin 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 codeendgoexec sp_testgo/*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_testDROP TABLE t_IC_ItemDROP TABLE t_UnitDROP TABLE t_BOMDROP TABLE t_BOMChildGO
[解决办法]
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