K3 BOM 多级展开SQL及实例分析,BOM数据接口_sql注入实例分析
K3 BOM 多级展开SQL及实例分析,BOM数据接口由刀豆文库小编整理，希望给你工作、学习、生活带来方便，猜你可能喜欢“sql注入实例分析”。
K3 BOM 多级展开SQL
CREATE TABLE t_IC_Item(FItemID int,FNumber varchar(200),FName varchar(200),FModel varchar(200),FUnitID int,FErpClsID int)
INSERT t_IC_Item
SELECT1,'CP.001','产品1','产品规格1',1,2 UNION SELECT2,'BC.001','半成品1' ,'半成品规格1' ,1,2 UNION SELECT3,'BC.002','半成品2' ,'半成品规格2' ,1,2 UNION SELECT4,'BC.003','半成品3' ,'半成品规格3' ,1,3 UNION SELECT5,'BC.004','半成品4' ,'半成品规格4' ,1,2 UNION SELECT6,'ZC.001','主材料1' ,'主材料规格1' ,1,1 UNION SELECT7,'ZC.002','主材料2' ,'主材料规格2' ,1,1 UNION SELECT8,'ZC.003','主材料3' ,'主材料规格3' ,1,1 UNION SELECT9,'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 ewhere
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
/*
IdLevelFItemIDFNameFModelFErpClsIDFQtyFName1Status-------------------------------------------------------------------------------------------------------
----------------------------------------
101产品1产品规格1自制1个正常
2.12半成品1半成品规格1自制1个正常
3..26主材料1主材料规格1外购1个4..27主材料2主材料规格2外购1个5.13半成品2半成品规格2自制1个正常
6..28主材料3主材料规格3外购1个7..29主材料4主材料规格4外购1个8..27主材料2主材料规格2外购1个9..24半成品3半成品规格3委外加工1个正常
10...310主材料5主材料规格5外购1个11...311主材料6主材料规格6外购1个
12...35半成品4半成品规格4自制1个BOM未建
13.115主材料10主材料规格10外购1个14.117主材料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
