drop table #tmp1999
drop table #tmp2999
drop table #tmp3999
drop table #tmp4999
drop table #tmp5999
drop table #tmp6999
drop table #tmp7999
drop table #tmp8999
drop table #tmp9999
drop table #tmp1
create table #tmp1999
(
[autoid] [int] IDENTITY(1,1) NOT NULL,
[opcomponentid] [nvarchar](100) NULL,
[bomid] [nvarchar](100) NULL,
[sortseq] [nvarchar](20) NULL,
[componentid] [nvarchar](100) NULL,
[baseqtyn] [decimal](10, 4) NULL,
[baseQtyD] [decimal](10, 4) NULL
)
delete #tmp1999
declare @id int
set @id=(select min(autoid) from SO_SODetails /*where cInvCode='1C00601009'*/ )
---声明一个变量，销售明细里面最小的ID号赋值给变量
while(@id)<=(select max(autoid) from SO_SODetails /*where cInvCode='1C00601009'*/)
---当@id<=销售订单明细表最大ID时，循环
begin
insert into #tmp1999 ( opcomponentid , bomid, sortseq , componentid, baseqtyn, baseQtyD)(
select opcomponentid , bomid, sortseq , componentid,
(baseqtYn*(select iQuantity from SO_SODetails where autoid=@id )) as baseqitn,
baseQtyD from bom_opcomponent
----查询BOM子表并插入到#Tmp1999中
where BomId =(
select MAX( BomId) as bomid from bom_parent
where ParentId = (
select partid from bas_part
where InvCode = (
select cinvcode from SO_SODetails
where AutoID=@id))))
set @id=@id+1
end
----根据#tmp1999创建一个数据结构相同的表 #tmp2999
select * into #tmp2999 from #tmp1999 where 1=0
/***
第二次循环，查找tmp#1999中的子件
***/
delete #tmp2999
set @id=(select min(autoid)from #tmp1999)
while @id<=(select max(autoid)from #tmp1999 )
begin
insert into #tmp2999 ( opcomponentid , bomid, sortseq , componentid, baseqtyn, baseQtyD)(
select opcomponentid , bomid, sortseq , componentid,
(baseqtYn*(select baseqtyn from #tmp1999 where autoid=@id )) as baseqitn
, baseQtyD from bom_opcomponent
----查询BOM子表并插入到Tmp2999中
where BomId =(select MAX( BomId) as bomid from bom_parent
where ParentId =(select ComponentId from #tmp1999 where AutoId=@id ) ))
set @id=@id+1
end
select * into #tmp3999 from #tmp1999 where 1=0
/***
第三次循环，查找tmp#2999中的子件
***/
delete #tmp3999
set @id=(select min(autoid)from #tmp2999)
while @id<=(select max(autoid)from #tmp2999 )
begin
insert into #tmp3999 ( opcomponentid , bomid, sortseq , componentid, baseqtyn, baseQtyD)(
select opcomponentid , bomid, sortseq , componentid,
(baseqtYn*(select baseqtyn from #tmp2999 where autoid=@id )) as baseqitn
, baseQtyD from bom_opcomponent
----查询BOM子表并插入到Tmp3999中
where BomId =(select MAX( BomId) as bomid from bom_parent
where ParentId =(select ComponentId from #tmp2999 where AutoId=@id ) ))
set @id=@id+1
end
select * into #tmp4999 from #tmp1999 where 1=0
/***
第四次循环，查找tmp#3999中的子件
***/
delete #tmp4999
set @id=(select min(autoid)from #tmp3999)
while @id<=(select max(autoid)from #tmp3999 )
begin
insert into #tmp4999 ( opcomponentid , bomid, sortseq , componentid, baseqtyn, baseQtyD)(
select opcomponentid , bomid, sortseq , componentid,
(baseqtYn*(select baseqtyn from #tmp3999 where autoid=@id )) as baseqitn
, baseQtyD from bom_opcomponent
----查询BOM子表并插入到Tmp4999中
where BomId =(select MAX( BomId) as bomid from bom_parent
where ParentId =(select ComponentId from #tmp3999 where AutoId=@id ) ))
set @id=@id+1
end
select * into #tmp5999 from #tmp1999 where 1=0
/***
第四次循环，查找tmp#4999中的子件
***/
delete #tmp5999
set @id=(select min(autoid)from #tmp4999)
while @id<=(select max(autoid)from #tmp4999 )
begin
insert into #tmp5999 ( opcomponentid , bomid, sortseq , componentid, baseqtyn, baseQtyD)(
select opcomponentid , bomid, sortseq , componentid,
(baseqtYn*(select baseqtyn from #tmp4999 where autoid=@id )) as baseqitn
, baseQtyD from bom_opcomponent
----查询BOM子表并插入到Tmp5999中
where BomId =(select MAX( BomId) as bomid from bom_parent
where ParentId =(select ComponentId from #tmp4999 where AutoId=@id ) ))
set @id=@id+1
end
select * into #tmp6999 from #tmp1999 where 1=0
/***
第六次循环，查找tmp#5999中的子件
***/
delete #tmp6999
set @id=(select min(autoid)from #tmp5999)
while @id<=(select max(autoid)from #tmp5999 )
begin
insert into #tmp6999 ( opcomponentid , bomid, sortseq , componentid, baseqtyn, baseQtyD)(
select opcomponentid , bomid, sortseq , componentid,
(baseqtYn*(select baseqtyn from #tmp5999 where autoid=@id )) as baseqitn
, baseQtyD from bom_opcomponent
----查询BOM子表并插入到Tmp5999中
where BomId =(select MAX( BomId) as bomid from bom_parent
where ParentId =(select ComponentId from #tmp5999 where AutoId=@id ) ))
set @id=@id+1
end
select * into #tmp7999 from #tmp1999 where 1=0
/***
第七次循环，查找tmp#6999中的子件
***/
delete #tmp7999
set @id=(select min(autoid)from #tmp6999)
while @id<=(select max(autoid)from #tmp6999 )
begin
insert into #tmp7999 ( opcomponentid , bomid, sortseq , componentid, baseqtyn, baseQtyD)(
select opcomponentid , bomid, sortseq , componentid,
(baseqtYn*(select baseqtyn from #tmp6999 where autoid=@id )) as baseqitn
, baseQtyD from bom_opcomponent
----查询BOM子表并插入到Tmp7999中
where BomId =(select MAX( BomId) as bomid from bom_parent
where ParentId =(select ComponentId from #tmp6999 where AutoId=@id ) ))
set @id=@id+1
end
select * into #tmp8999 from #tmp1999 where 1=0
/***
第八次循环，查找tmp#7999中的子件
***/
delete #tmp8999
set @id=(select min(autoid)from #tmp7999)
while @id<=(select max(autoid)from #tmp7999 )
begin
insert into #tmp8999 ( opcomponentid , bomid, sortseq , componentid, baseqtyn, baseQtyD)(
select opcomponentid , bomid, sortseq , componentid,
(baseqtYn*(select baseqtyn from #tmp7999 where autoid=@id )) as baseqitn
, baseQtyD from bom_opcomponent
----查询BOM子表并插入到Tmp7999中
where BomId =(select MAX( BomId) as bomid from bom_parent
where ParentId =(select ComponentId from #tmp7999 where AutoId=@id ) ))
set @id=@id+1
end
select * into #tmp9999 from #tmp1999 where 1=0
/***
第9次循环，查找tmp#8999中的子件
***/
delete #tmp9999
set @id=(select min(autoid)from #tmp8999)
while @id<=(select max(autoid)from #tmp8999 )
begin
insert into #tmp9999 ( opcomponentid , bomid, sortseq , componentid, baseqtyn, baseQtyD)(
select opcomponentid , bomid, sortseq , componentid,
(baseqtYn*(select baseqtyn from #tmp8999 where autoid=@id )) as baseqitn
, baseQtyD from bom_opcomponent
----查询BOM子表并插入到Tmp7999中
where BomId =(select MAX( BomId) as bomid from bom_parent
where ParentId =(select ComponentId from #tmp8999 where AutoId=@id ) ))
set @id=@id+1
end
select * into #tmp1 from #tmp1999 where 1=0
set @id=(select count(autoid) from #tmp9999 )
if @id=0
begin
delete #tmp1
insert into #tmp1 ( opcomponentid , bomid, sortseq , componentid, baseqtyn, baseQtyD)
(select opcomponentid , bomid, sortseq , componentid, baseqtyn, baseQtyD from #tmp1999)
insert into #tmp1 ( opcomponentid , bomid, sortseq , componentid, baseqtyn, baseQtyD)
(select opcomponentid , bomid, sortseq , componentid, baseqtyn, baseQtyD from #tmp2999)
insert into #tmp1 ( opcomponentid , bomid, sortseq , componentid, baseqtyn, baseQtyD)
(select opcomponentid , bomid, sortseq , componentid, baseqtyn, baseQtyD from #tmp3999)
insert into #tmp1 ( opcomponentid , bomid, sortseq , componentid, baseqtyn, baseQtyD)
(select opcomponentid , bomid, sortseq , componentid, baseqtyn, baseQtyD from #tmp4999)
insert into #tmp1 ( opcomponentid , bomid, sortseq , componentid, baseqtyn, baseQtyD)
(select opcomponentid , bomid, sortseq , componentid, baseqtyn, baseQtyD from #tmp5999)
insert into #tmp1 ( opcomponentid , bomid, sortseq , componentid, baseqtyn, baseQtyD)
(select opcomponentid , bomid, sortseq , componentid, baseqtyn, baseQtyD from #tmp6999)
insert into #tmp1 ( opcomponentid , bomid, sortseq , componentid, baseqtyn, baseQtyD)
(select opcomponentid , bomid, sortseq , componentid, baseqtyn, baseQtyD from #tmp7999)
insert into #tmp1 ( opcomponentid , bomid, sortseq , componentid, baseqtyn, baseQtyD)
(select opcomponentid , bomid, sortseq , componentid, baseqtyn, baseQtyD from #tmp8999)
insert into #tmp1 ( opcomponentid , bomid, sortseq , componentid, baseqtyn, baseQtyD)
(select opcomponentid , bomid, sortseq , componentid, baseqtyn, baseQtyD from #tmp9999)
end
/***
select * from #tmp1
where componentid='35547'
select * from bas_part
where invcode='3020900005' partid='33857'
***/
