sql server 游标 实现从一堆小数中最小加最大不超过一,小于一继续加第二小的,大于一加返回最小的加第二大的

时间:2021-12-02 03:31:50
ALTER procedure [dbo].[maoPaoTest3]
 as 
declare 
  @ratio decimal(7,4),
  @addCount1  decimal(7,4),
  @addCount2  decimal(7,4),
  @iTrayNum int ,                 --托号
  @produce nvarchar(30),          --产品
  @boxnum int                     --当前托上剩余量
  
  create table table1 (ID int identity(1,1),produce nvarchar(30),boxnum int,
                       evetbouxnumb int,ratio decimal(7,4),tptype varchar(30))
  insert into table1 select 
                        cr.produce,
                        cr.boxnum,
                        cr.evetbouxnumb,
                        convert(decimal(7,4),
                        convert(decimal(7,4),cr.boxnum) / convert(decimal(7,4),cr.evetbouxnumb)) ratio,
                        cr.tptype                             
                     from yy_ContractResidue cr order by ratio asc 


  select * from table1
  --delete from table1 where ratio = (select top 1 ratio from table1 order by id desc )
  --select * from table1
  --select top 1 ratio from table1 order by id desc
  --select sum(ratio) from table1
   set @iTrayNum = 20
  if (select sum(ratio) from table1) <= 1
    begin
      insert into yy_PackageListC select * from table1
      insert into yy_PackageListC(tpnum) values (@iTrayNum)
    end 
  else
    begin   
      declare pcurr cursor for  select produce,boxnum,ratio from table1  
open pcurr
      set @addCount1 = 0
      --set @addCount2 = 0
fetch next from pcurr into @produce,@boxnum,@ratio 
      set @addCount1 = @ratio +  (select top 1 ratio from table1 order by id desc )  
while (@@fetch_status = 0)
begin 
            --set @addCount2 = @addCount1 + @addCount2 
           if @addCount1 < 1
             begin 
                   print (@addCount1)
             
                fetch next from pcurr into @produce,@boxnum,@ratio
                set @addCount1 = @ratio + @addCount1
                --print (@addCount1)          
             end
            
           if @addCount1 = 1
             begin 
               delete from table1 where ratio = (select top 1 ratio from table1 order by id desc )
                 fetch next from pcurr into @produce,@boxnum,@ratio 
                set @addCount1 = @ratio + (select top 1 ratio from table1 order by id desc ) 
             end        


           if @addCount1 > 1 
             begin
                  --print (@addCount1) 
              delete from table1 where ratio = (select top 1 ratio from table1 order by id desc )
                 --fetch next from pcurr into @produce,@boxnum,@ratio 
                set @addCount1 = @ratio + (select top 1 ratio from table1 order by id desc ) 
                --print (@addCount1) 
                -- fetch next from pcurr into @produce,@boxnum,@ratio 
             end 
            
           /* 
           if @addCount = 1
             begin 
              set @iTrayNum = @iTrayNum + 1
              insert into yy_PackageListC(produce,boxnum,tpnum) values (@produce,@boxnum,@iTrayNum)
              insert into yy_PackageListC(produce,boxnum,tpnum) values ((select top 1 produce from table1 order by id desc),
                                                                        (select top 1 boxnum from table1 order by id desc),
                                                                         @iTrayNum)
              delete from table1 where ratio = @ratio
              delete from table1 where ratio = (select top 1 ratio from table1 order by id desc ) 
              fetch next from pcurr into @produce,@boxnum,@ratio                                                             
             end
           if @addCount > 1 
             begin 
               --print ('上面的数据是余数表各系数最佳拼托组合之和')
               set @iTrayNum = @iTrayNum 
               insert into yy_PackageListC(produce,boxnum,tpnum) values ((select top 1 produce from table1 order by id desc),
                                                                        (select top 1 boxnum from table1 order by id desc),
                                                                         @iTrayNum)
               delete from table1 where ratio = (select top 1 ratio from table1 order by id desc )
               --set @addCount = 0
               --set @addCount = @ratio + @addCount 
               print (@addCount)
             end         */
      
           --fetch next from pcurr into @produce,@boxnum,@ratio          
      end
    end 
close pcurr
deallocate pcurr

 drop table table1 

/*因为其中含有部分项目属性代码,懒得删。。*/