高分再次求教背包问题

[leitnt]

有这样一个表

编号 单价
-------------
001 1.2
002 34.5
003 11.3
004 25
005 56.8
…… ……
…… ……
-------------



如果想取其中12条记录，且合计为329（通过数量*单价实现），

编号 数量 单价 金额
------------------------------
022 2 18.00 36.00
140 4 2.00 8.00
011 1 8.70 8.70
002 2 35.00 70.00
142 1 20.90 20.90
146 5 5.30 26.50
099 3 17.70 53.10
121 2 0.70 1.40
038 2 5.20 10.40
299 5 6.20 31.00
183 2 9.00 18.00
299 5 9.00 45.00
-------------------------------

能否通过调整数量使得金额合计等于特定值？注意：取12条记录，且合计是329.最好是sql2000代码

[nianran520]

if object_id('tb') is not null drop table tb
create table tb (code varchar(10),price dec(5,2))
insert into tb
select '001',    1.22 union all
select '002'   , 34.5 union all
select '003'   , 11.3 union all
select '004'  ,  25 union all
select '005'  ,  56.8 union all
select '022' ,18.00 union all 
select '140'  ,  2.00 union all 
select '011' ,   8.70 union all 
select '006'  ,   35.00 union all 
select '142'  ,   20.90 union all 
select '146'  ,  5.30 union all 
select '099'  ,  17.70 union all 
select '121'  , 0.70 union all
select '038'  , 5.20 union all
select '299'  , 6.20 union all 
select '183'  , 9.00 union all 
select '299'  , 9.01 

declare @count int,@row int,@i int,@id int --总行数、随机数、取出行数、匹配的行数
declare @nums dec(18,2) --总金额
declare @remain dec(18,2),@max_price dec(18,2) -- 剩余金额、最大单价
declare @mintotal dec(18,2)
--初始值
select @i = 1,
@nums = 9.9, 
@count = count(1) from tb
--生成id，随机取值用
select identity(int,1,1) as id,* into #temp from tb order by price
select @mintotal = sum(price) from #temp where id <= 12
if @nums < @mintotal
begin
	select '输入金额小于临界值'
	return
end
--创建临时表
create table #temp_tb (id int,code varchar(10),nums int,price dec(5,2),total as price*nums)

select @remain = @nums
while @remain <> 0 
begin
	--随机取12行
	while @i <= 12
	begin
		select @row = convert(int,(rand(checksum(newid()))*@count)) + 1
		if EXISTS (select 1 from #temp_tb where id = @row)
		begin
			select @row = min(id) from #temp 
			where id not in (select id from #temp_tb)
		end
		insert into #temp_tb(id,code,nums,price)
		select id,code,1,price from #temp where id = @row 
		select @i = @i + 1  
	end
	--剩余金额
	select @remain = @nums - sum(nums*price) from #temp_tb
	select @max_price = avg(price) from #temp_tb
	--分配nums值
	while @remain > @max_price
	begin
		update #temp_tb set nums = nums+1
		where id = convert(int,(rand(checksum(newid()))*@count)) + 1
		select @remain = @nums - sum(nums*price) from #temp_tb
	end
	--获取匹配行
	select @id =  max(id) from #temp_tb where @remain/price = cast(@remain/price as int)
	and @remain > 0 
	--有
	if @id is not null
	begin
		update #temp_tb set nums = nums + @remain/price
		where  id = @id
		select @remain = 0
	end
	--无则重新循环
	else
	begin
		delete from #temp_tb
		select @i = 1
		select @remain = @nums
	end
end
select sum(total) as 总金额 from #temp_tb
select * from #temp_tb
drop table #temp,#temp_tb

[leitnt]

楼上重复操作会出现错误。如果确实无法找到匹配项（例如要取总金额100），应该从哪儿退出循环呢？

[nianran520]

--定义参数那加上
declare @times int
select @times = 0
--这里加
	--无则重新循环
	else
	begin
		delete from #temp_tb
		select @i = 1
		select @remain = @nums
		select @times = @times + 1
		if @times = 1000 
		begin
			break
		end
	end

[nianran520]

if @nums < @mintotal
begin
	select '输入金额小于临界值'
	drop table #temp --这里加上这句
	return
end

[nianran520]

--应该没什么问题，有其他需求自己再改改

[让你望见影子的墙]

.

[icelovey]

坐等高手啦~

[renadg]

高手快来,快来!!!

[kldeng_05]

Mark,等高手~

[guguda2008]

上次不是写过吗？没人写下半段？

[zhouyongyang]

我也支持一下吧，学习

[envykok]

人工智能 -- 先把所有记录拿出再用搜索 树 吧

[syw_java]

有难度

[Mirana_NightShade]

up

[ysx003]

呵呵 我是初学者
进来看看
帮不上忙
不好意思

[nianran520]

--最终版
/*
dbo.proc_trans_makedata '@table 

'
*/
/*
--By Ken 2009-11-25
--背包问题，HRC可能用到
*/
if object_id('tb') is not null drop table tb
create table tb (code varchar(10),price dec(5,2))
insert into tb
select '001',    1.22 union all
select '002'   , 34.5 union all
select '003'   , 11.3 union all
select '004'  ,  25 union all
select '005'  ,  56.8 union all
select '022' ,18.00 union all 
select '140'  ,  2.00 union all 
select '011' ,   8.70 union all 
select '006'  ,   35.00 union all 
select '142'  ,   20.90 union all 
select '146'  ,  5.30 union all 
select '099'  ,  17.70 union all 
select '121'  , 0.70 union all
select '038'  , 5.20 union all
select '299'  , 6.20 union all 
select '183'  , 9.00 union all 
select '299'  , 9.01 

declare @count int,@row int,@i int,@id int --总行数、随机数、取出行数、匹配的行数
declare @nums dec(18,2),@rand dec(18,2) --总金额
declare @remain dec(18,2),@max_price dec(18,2) -- 剩余金额、最大单价
declare @mintotal dec(18,2)
declare @times int
declare @multiple int
--初始值
select @i = 1,@times = 0,
@nums = 2200.52, 
@count = count(1) from tb
--生成id，随机取值用
select identity(int,1,1) as id,* into #temp from tb order by price
select @mintotal = sum(price) from #temp where id <= 12
if @nums < @mintotal
begin
	--select '输入金额小于临界值'
	drop table #temp
	return
end
--创建临时表
create table #temp_tb (id int,code varchar(10),nums int,price dec(5,2),total as price*nums)

select @remain = @nums
while @remain <> 0 
begin
	--随机取12行
	while @i <= 12
	begin
		select @row = convert(int,(rand(checksum(newid()))*@count)) + 1
		if EXISTS (select 1 from #temp_tb where id = @row)
		begin
			select @row = min(id) from #temp 
			where id not in (select id from #temp_tb)
		end
		insert into #temp_tb(id,code,nums,price)
		select id,code,1,price from #temp where id = @row 
		select @i = @i + 1  
	end
	--倍数
	select @rand = sum(price) from #temp_tb
	select @multiple = cast(@nums/@rand as int) - 5 --可自选
	if @multiple > 0
	begin
		update #temp_tb set nums = @multiple
	end
	--剩余金额
	select @remain = @nums - sum(nums*price) from #temp_tb
	select @max_price = avg(price) from #temp_tb
	--分配nums值
	while @remain > @max_price
	begin
		update #temp_tb set nums = nums+1
		where id = convert(int,(rand(checksum(newid()))*@count)) + 1
		select @remain = @nums - sum(nums*price) from #temp_tb
	end
	--获取匹配行
	select @id =  max(id) from #temp_tb where @remain/price = cast(@remain/price as int)
	and @remain > 0 
	--有
	if @id is not null
	begin
		update #temp_tb set nums = nums + @remain/price
		where  id = @id
		select @remain = 0
	end
	--无则重新循环
	else
	begin
		delete from #temp_tb
		select @i = 1
		select @remain = @nums
		select @times = @times + 1
		if @times = 1000 --设置循环次数，匹配不了就退出循环
		begin
			break
		end
	end
end
select sum(total) as 总金额 from #temp_tb
select * from #temp_tb
drop table #temp,#temp_tb

[beddevil]

学习

[yan11cn]

学习学习

[icelovey]

牛~`

[z812183667]

学习！！！