--創建一個臨時表,用來保存對應的手冊號
create table #tempt3(PZHao int,[count] numeric(15,5),manualNo varchar(20))
--保存到臨時表中
insert into #tempt3
select b.PZHao,b.count,c.manualNo from Finished a left join #tempt2 b on a.PZHao=b.PZHao
inner join BaseInfo c on a.baseInfoId=c.baseInfoId
where b.PZHao is not null and
a.baseInfoId in(select top 1 baseInfoId from BaseInfo where contractStatus='1' order by baseInfoId)
--select * from #tempt3
--獲取第二個憑證號對應的手冊號
--這個地方可以循環插入的
declare @rount int ,@jilu int, int
set @rount=0
set @jilu=1
set @num=0
select @rount=count(*) from BaseInfo where contractStatus ='1'
while @jilu<@rount
begin
print @jilu
insert into #tempt3
select distinct b.PZHao,b.count,c.manualNo from Finished a left join #tempt2 b on a.PZHao=b.PZHao
inner join BaseInfo c on a.baseInfoId=c.baseInfoId
where b.PZHao is not null and
c.manualNo in(select top 1 manualNo from BaseInfo where contractStatus ='1' and
manualNo not in(select manualNo from #tempt3) order by baseInfoId )
and b.PZHao not in(select PZHao from #tempt3 )
order by b.PZHao
set @jilu=@jilu+1
end
--select * from #tempt3
select @num=count(manualno) from Repertoy_package_materials_sum where manualno in (select manualno from #tempt3)
if @num>0
begin
delete from Repertoy_package_materials_sum where manualno in (select manualno from #tempt3)
insert into Repertoy_package_materials_sum(PZHao,product_count,manualno)
select * from #tempt3
end
else
begin
insert into Repertoy_package_materials_sum(PZHao,product_count,manualno)
select * from #tempt3
end