日期:2014-05-18  浏览次数:20446 次

高人来啊~求一条SQL语句~
表movie中
id       mnet
1           10
2           20
3           20
4           30
5           40
6           10
7           10
8           20
9           20
10         20
取5条出来,让其mnet总和等于100
如:id=2,id=4,id=6,id=8,id=9就是一组符合条件的
请教ING...
谢谢了~~


------解决方案--------------------
select
a.id,b.id,c.id,d.id,e.id
from
movie a,
movie b,
movie c,
movie d,
movie e
where
a.id <b.id and b.id <c.id and c.id <d.id and d.id <e.id
and
a.mnet+b.mnet+c.mnet+d.mnet+e.mnet=100
------解决方案--------------------

declare @sum int
declare @id varchar(50)
declare @table table([id] int, mnet int)

while(1=1)
begin
insert into @table([id], mnet) select top 5 [id], mnet from t_test order by newid()

select @sum = sum(mnet) from @table
if(@sum = 100)
break

delete @table
end

select * from @table