有表如下：

to:
yesterday2000(一笑而过) 我不是要每个商品的汇总的金额，我要的是一组商品，而且汇总金额刚好等于一个固定值的记录to:
realgz(realgz) 实际情况是这样的：金额一般都在是几元或几十元之间，而固定的金额在25万左右。所以说用几个表自连按也就是取的记录不是几条或几十条所用达到的

如果这样，最简单的办法，做个贪心算法来删除。
declare @WantToDelete money
,@TmpSum money
,@cnt int
,@cnthold int
,@loopcnt int
select @cnthold=1
select *,identity(int,1,1) cnt into #t from Sales order by new_id() --本句作用是增加删除随机性，建主键可以增加性能
create select top 0 * into #t2  from #t --要删除的记录select @cnt=b.SaleID,@TmpSum=sum(a.Money) --取出最接近的集合
from #t2 a,#t2 b
where a.cnt<b.cnt
group by b.SaleID
having sum(a.Money) < =@WantToDelete

insert into #t2  select * from #t where cnt <@cnt
delete from  #t where cnt <@cnt
while @TmpSum <> @WantToDelete and @cnthold<@cnt --如果是接近不是等于
begin
begin tran
--`利用事务来后悔
delete from #t2 where cnt>@cnt-@cnthold --第一次尝试直接搜索,从#套理删除0挑记录并放回#t,第二次1条……
insert into #t select *  from  #t2 where cnt>@cnt-@cnthold

select @TmpSum=sum(Money) from Sales where SaleID in (select SaleID from #t2)
select @loopcnt=@cnthold while @loopcnt>0 --循环次数
begin
if @loopcnt=1 --如果是最后一次循环,直接搜索
begin
insert into #t2
select top 1 SaleID from Sales where cnt>=@cnt and Money =@WantToDelete-@TmpSum
if @@rowcount=1
begin
select @TmpSum=@WantToDelete
break
end
end
else --否则只搜索一笔,
begin
insert into #t2 --尝试直接搜索
select top 1 SaleID from Sales where cnt>=@cnt and Money =@WantToDelete-@TmpSum
if @@rowcount=1 --获得,退出
begin
select @TmpSum=@WantToDelete
break
end
insert into #t2 --继续正常搜索
select top 1 * from #t where cnt>=@cnt and Money <=@WantToDelete-@TmpSum order by Money desc
delete from #t where cnt in (select cnt from #t2)

select @TmpSum =sum(Money) from Sales where cnt in (select cnt from #t2)
if @TmpSum=@WantToDelete --万一刚好
break
end
select @loopcnt=@loopcnt-1
end
select @cnthold=@cnthold+1
if @TmpSum <> @WantToDelete --如果搜索成功 ,应用事务,否则回滚,无论如何进行下一次搜索
begin
rollback tran
end
else
begin commit tran
end
select @cnthold =@cnthold+1
end
delete from Sales where SaleID in (select SaleID from #T2)

declare @WantToDelete money
,@TmpSum money
,@cnt int
,@cnthold int
,@loopcnt int
select @cnthold=1
select *,identity(int,1,1) cnt into #t from Sales order by new_id() --本句作用是增加删除随机性，建主键可以增加性能
select top 0 * into #t2  from #t --要删除的记录select @cnt=b.SaleID,@TmpSum=sum(a.Money) --取出最接近的集合
from #t2 a,#t2 b
where a.cnt<b.cnt
group by b.SaleID
having sum(a.Money) < =@WantToDelete

insert into #t2  select * from #t where cnt <@cnt
delete from  #t where cnt <@cnt
while @TmpSum <> @WantToDelete and @cnthold<@cnt --如果是接近不是等于
begin
begin tran
--`利用事务来后悔
delete from #t2 where cnt>@cnt-@cnthold --第一次尝试直接搜索,从#套理删除0挑记录并放回#t,第二次1条……
insert into #t select *  from  #t2 where cnt>@cnt-@cnthold

select @TmpSum=sum(Money) from Sales where SaleID in (select SaleID from #t2)
select @loopcnt=@cnthold while @loopcnt>0 --循环次数
begin
insert into #t2 --尝试直接搜索
select top 1 SaleID from Sales where cnt>=@cnt and Money =@WantToDelete-@TmpSum
if @@rowcount=1 --获得,退出
begin
select @TmpSum=@WantToDelete
break
end
insert into #t2 --继续正常搜索
select top 1 * from #t where cnt>=@cnt and Money <=@WantToDelete-@TmpSum order by Money desc
delete from #t where cnt in (select cnt from #t2)

select @TmpSum =sum(Money) from Sales where cnt in (select cnt from #t2)
if @TmpSum=@WantToDelete --万一刚好
break select @loopcnt=@loopcnt-1
end
select @cnthold=@cnthold+1
if @TmpSum <> @WantToDelete --如果搜索成功 ,应用事务,否则回滚,无论如何进行下一次搜索
begin
rollback tran
end
else
begin commit tran
end
select @cnthold =@cnthold+1
end
delete from Sales where SaleID in (select SaleID from #T2)

修正下算法：
declare @WantToDelete money
,@TmpSum money
,@cnt int
,@cnthold int
,@loopcnt int
select @cnthold=1
select *,identity(int,1,1) cnt into #t from Sales order by new_id() --±¾¾ä×÷ÓÃÊÇÔö¼ÓÉ¾³ýËæ»úÐÔ£¬½¨Ö÷¼ü¿ÉÒÔÔö¼ÓÐÔÄÜ
select top 0 * into #t2  from #t --ÒªÉ¾³ýµÄ¼ÇÂ¼select @cnt=b.SaleID,@TmpSum=sum(a.Money) --È¡³ö×î½Ó½üµÄ¼¯ºÏ
from #t2 a,#t2 b
where a.cnt<b.cnt
group by b.SaleID
having sum(a.Money) < =@WantToDelete

insert into #t2  select * from #t where cnt <@cnt
delete from  #t where cnt <@cnt
while @TmpSum <> @WantToDelete and @cnthold<@cnt --Èç¹ûÊÇ½Ó½ü²»ÊÇµÈÓÚ
begin
begin tran
--`ÀûÓÃÊÂÎñÀ´ºó»Ú
delete from #t2 where cnt>@cnt-@cnthold --µÚÒ»´Î³¢ÊÔÖ±½ÓËÑË÷,´Ó#Ì×ÀíÉ¾³ý0Ìô¼ÇÂ¼²¢·Å»Ø#t,µÚ¶þ´Î1Ìõ¡¡
insert into #t select *  from  #t2 where cnt>@cnt-@cnthold

select @TmpSum=sum(Money) from Sales where SaleID in (select SaleID from #t2)
select @loopcnt=@cnthold while @loopcnt>0 --Ñ»·´ÎÊý
begin
insert into #t2 --³¢ÊÔÖ±½ÓËÑË÷
select top 1 SaleID from #t where cnt  not in  (select cnt  from #t2) and Money =@WantToDelete-@TmpSum
if @@rowcount=1 --»ñµÃ,ÍË³ö
begin
select @TmpSum=@WantToDelete
break
end
insert into #t2 --¼ÌÐøÕý³£ËÑË÷
select top 1 * from #t where cnt  not in  (select cnt  from #t2) and Money < @WantToDelete-@TmpSum
delete from #t where cnt in (select cnt from #t2)

select @loopcnt=@loopcnt-1
end

if @TmpSum <> @WantToDelete --Èç¹ûËÑË÷³É¹¦ ,Ó¦ÓÃÊÂÎñ,·ñÔò»Ø¹ö,ÎÞÂÛÈçºÎ½øÐÐÏÂÒ»´ÎËÑË÷
begin
rollback tran
end
else
begin commit tran
end
select @cnthold =@cnthold+1
end
delete from Sales where SaleID in (select SaleID from #T2)

--修正算法，怎么乱码了？
declare @WantToDelete money
,@TmpSum money
,@cnt int
,@cnthold int
,@loopcnt int
select @cnthold=1
select *,identity(int,1,1) cnt into #t from Sales order by new_id() --本句作用是增加删除随机性，建主键可以增加性能
select top 0 * into #t2  from #t --要删除的记录select @cnt=b.SaleID,@TmpSum=sum(a.Money) --取出最接近的集合
from #t2 a,#t2 b
where a.cnt<b.cnt
group by b.SaleID
having sum(a.Money) < =@WantToDelete

insert into #t2  select * from #t where cnt <@cnt
delete from  #t where cnt <@cnt
while @TmpSum <> @WantToDelete and @cnthold<@cnt --如果是接近不是等于
begin
begin tran
--`利用事务来后悔
delete from #t2 where cnt>@cnt-@cnthold --第一次尝试直接搜索,从#套理删除0挑记录并放回#t,第二次1条……
insert into #t select *  from  #t2 where cnt>@cnt-@cnthold

select @TmpSum=sum(Money) from Sales where SaleID in (select SaleID from #t2)
select @loopcnt=@cnthold while @loopcnt>0 --循环次数
begin
insert into #t2 --尝试直接搜索
select top 1 SaleID from #t where cnt  not in  (select cnt  from #t2) and Money =@WantToDelete-@TmpSum
if @@rowcount=1 --获得,退出
begin
select @TmpSum=@WantToDelete
break
end
insert into #t2 --继续正常搜索
select top 1 * from #t where cnt  not in  (select cnt  from #t2) and Money < @WantToDelete-@TmpSum
delete from #t where cnt in (select cnt from #t2)

select @loopcnt=@loopcnt-1
end

if @TmpSum <> @WantToDelete --如果搜索成功 ,应用事务,否则回滚,无论如何进行下一次搜索
begin
rollback tran
end
else
begin commit tran
end
select @cnthold =@cnthold+1
end
delete from Sales where SaleID in (select SaleID from #T2)

select @cnt=b.SaleID,@TmpSum=sum(a.Money) --取出最接近的集合
from #t2 a,#t2 b
where a.cnt<b.cnt
group by b.SaleID
having sum(a.Money) < =@WantToDelete
= --连表名都弄错！！
select @cnt=b.SaleID,@TmpSum=sum(a.Money) --取出最接近的集合
from #t a,#t b
where a.cnt<b.cnt
group by b.SaleID
having sum(a.Money) < =@WantToDelete

根据题意
txliceuhe(决不冒牌马可) 的方法有损压缩了搜索次数（可能导致搜索失败），但是如果要完全精确确定，假设记录条数为n那要考察2的n次方－1种组合才能遍历所有的可能性，那已经不是天文数字的问题而是根本不可能的问题。如果可以删除倒最接近的要求（用 txliceuhe(决不冒牌马可) 的思路），然后可以选择再删还是加记录，那性能会达到可以接受的范围。
declare @WantToDelete money
,@TmpSum money
,@cnt int
,@cnthold int
,@loopcnt int
select @cnthold=1
select *,identity(int,1,1) cnt into #t from Sales order by new_id() --本句作用是增加删除随机性，建主键可以增加性能
select top 0 * into #t2 from #t --要删除的记录select top 1 @cnt=b.SaleID,@TmpSum=sum(a.Money) --取出最接近的集合
from #t2 a,#t2 b
where a.cnt<b.cnt
group by b.SaleID
having sum(a.Money) >@WantToDelete
order by sum(a.Money)
insert into #t2 select * from #t where cnt <@cnt 然后开始进入修正过程，最简单的修正是找个1分的商品，看@WantToDelete,@TmpSum 的差别是多少就给他补多少进去，哈哈

select top 1 @cnt=b.SaleID,@TmpSum=sum(a.Money) --取出最接近的集合
from #t a,#t b
where a.cnt<b.cnt
group by b.SaleID
having sum(a.Money) >@WantToDelete
order by sum(a.Money)
insert into #t2 select * from #t where cnt <@cnt

解决方案 »