最优逼近算法-用SP实现

name是货名，volume是货物的体积
表T中有N行货物
运输货物的集装箱为68立方米
要求：用存储过程实现的算法来输出一份最大限度利用集装箱空间的货物单

---------------------------------------------------------------------
---------------------------------------------------------------------
create table tt(name varchar(10),volume decimal(9,2))
insert tt select '1','6.24'
union select '3','8.96'
union select '4','8.10'
union select '5','4.37'
union select '6','10.56'
union select '7','50.89'
union select '8','6.24'
union select '9','8.96'
union select '10','8.10'
union select '11','4.37'
union select '12','10.56'
union select '13','50.89'

select * into # from tt where volume<=68
create table #sql(coid int identity(1,1), co varchar(2000))

declare @int decimal(9,2),@max int,@sql varchar(2000),@sqldelete varchar(100)
select @int=max(volume) from #

 

while(exists(select 1 from #))
begin
  select top 1 @sql=name,@sqldelete=name
    from # where volume in(select max(volume) from #)

  delete # where name=@sqldelete

    while(exists(select 1 from # where volume<=68-@int))
    begin
      set @max=68-@int
      select @int=@int+max(volume) from # where volume<=@max

      select top 1 @sql=@sql+','+name,@sqldelete=name
        from # where volume in(select max(volume) from # where volume<=@max)

      delete # where name=@sqldelete
    end

  insert into #sql values(@sql)
  select @int=max(volume) from # where volume<=68
end

select * from #sql

select a.*,b.coid from tt a left join #sql b on CHARINDEX(','+a.name+',',','+b.co+',')>0
order by b.coid

drop table #
drop table #sql