如何取出sum后小于一定数值的记录？！！！
/*取出sum后小于一定数值的记录*/
create table test
(id int,
quantity float
)
insert into test
select 1,2 union all
select 2,3 union all
select 3,4 union all
select 4,5 union all
select 5,6 union all
select 6,7 union all
select 7,8 union all
select 8,9 union all
select 9,10
--问题：任意取出一些记录，使得这些及记录的和（sum）小于某一个用户指定的值（如14）
[解决办法]
create table test
(id int,
quantity float
)
insert into test
select 1,2 union all
select 2,3 union all
select 3,4 union all
select 4,5 union all
select 5,6 union all
select 6,7 union all
select 7,8 union all
select 8,9 union all
select 9,10
declare @int int
set @int='14'
select * from test a where (select sum(quantity) from test where id<a.id)<@int
/*
-----------------
id quantity
23.0
34.0
45.0
------------------
*/
declare @test table
(
id int,
quantity float
)
insert into test
select 1,2 union all
select 2,3 union all
select 3,4 union all
select 4,5 union all
select 5,6 union all
select 6,7 union all
select 7,8 union all
select 8,9 union all
select 9,10
declare @int int
set @int='14'
declare @a int
set @a=1
while ((select count(*) from @test)>1)
begin
while((select sum(quantity) from (select top(@a) * from @test)a)<@int)
begin
select top(@a)* from @test
set @a=@a+1
end
delete @test where id=(select top(1) id from @test)
set @a=1
end
