***********************请教一个sql查询的问题**************************
表a
id amount
1 3
2 15
3 20
4 28
给一个数字@b = 30
要查出b在对amount求和的哪条记录的范围内
比如 1 + 15 + 20 > 30并且 1 + 15 < 30
那么@b就应该在3这个id范围内
不知道我说清楚意思没有
谢谢各位
[解决办法]
declare @t table(id int,amount int)
insert into @t select 1,3
insert into @t select 2,15
insert into @t select 3,20
insert into @t select 4,28
declare @a int,@b int,@id int
set @a=0
set @b=30
select
@id=case
when @b> @a and @b <=@a+amount then id
else @id
end,
@a =@a+amount
from
@t
select @id as id
/*
id
-----------
3
*/
[解决办法]
declare @t table(id int,amount int)
insert into @t select 1,3
insert into @t select 2,15
insert into @t select 3,20
insert into @t select 4,28
select a.id from @t a
where (select sum(amount) from @t where id <a.id) <30 and (select sum(amount) from @t where id <=a.id)> =30
