上面有点错误,
修正
计算过程是
n_id num sum
1 5 32
2 3 27
3 6 24 ....
4 3 18 = sum(num) where n_id >=4 and n_id<=7
5 5 15 = sum(num) where n_id >=5 and n_id<=7
6 4 10 = sum(num) where n_id >=6 and n_id<=7
7 6 6 从n_id=7开始计算
8 10
--------
上面纪录中第三条sum值是24,是第一个大于提供数字21的纪录
要求sql语句查询得出从第三条到第七条的所有纪录
修正
计算过程是
n_id num sum
1 5 32
2 3 27
3 6 24 ....
4 3 18 = sum(num) where n_id >=4 and n_id<=7
5 5 15 = sum(num) where n_id >=5 and n_id<=7
6 4 10 = sum(num) where n_id >=6 and n_id<=7
7 6 6 从n_id=7开始计算
8 10
--------
上面纪录中第三条sum值是24,是第一个大于提供数字21的纪录
要求sql语句查询得出从第三条到第七条的所有纪录
n_id num1 num2
1 5 0
2 3 8
3 6 10
4 3 16
5 5 15
6 4 12
7 6 21
8 10 18
...提供一个参数 n_id 为7
求出上面数据中的部分纪录
计算过程是
n_id num1 num2 sum
1 5 0 32
2 3 8 27
3 6 10 24 ....
4 3 16 18 = sum(num) where n_id >=4 and n_id<=7
5 5 15 15 = sum(num) where n_id >=5 and n_id<=7
6 4 12 10 = sum(num) where n_id >=6 and n_id<=7
7 6 21 6 从n_id=7开始计算
8 10 18
--------
上面纪录中第三条sum值是24,是第一个大于查询纪录第七条n_id7的num2数字21的纪录
要求sql语句查询得出从第三条到第七条的所有纪录
from 表 a
create proc p_qry
@n_id int,
@num int
as
select * into #t from 表 where n_id<=@nid order by n_id desc
declare @a int,@b int
set @a=0
update #t set @b=@a+num,@a=num,num=@bselect * from(
select top 3 * from #t where num=@num
) a order by n_id desc
go--调用示例
exec @n_id=7,@num=21
(case when a.n_id>7 then null else
(select sum(b.num) from yourtablename b where b.n_id>=a.n_id and b.n_id<=7) end) as [sum]
from yourtablename a order by a.n_id
select *,(select sum(num) from 你的表 where n_id between tem.n_id and 7) 结果 from 你的表 tem where n_id<=7
) a where 结果<=21
--数据处理存储过程
create proc p_qry
@n_id int,
@num int
as
select *,aa=0 into #t from t where n_id<=@n_id order by n_id desc
declare @a int,@b int
set @a=0
update #t set @b=@a+num,aa=@b,@a=@b
select * from(
select top 3 n_id,num from #t where aa>@num
) a order by n_id
go--调用示例
exec p_qry @n_id=7,@num=21
insert 你的表 values(1 , 5)
insert 你的表 values(2 , 3)
insert 你的表 values(3 , 6 )
insert 你的表 values(4 , 3)
insert 你的表 values(5 , 5)
insert 你的表 values(6 , 4)
insert 你的表 values(7 , 6)
insert 你的表 values(8, 10)
select * from (
select *,(select sum(num) from 你的表 where n_id between tem.n_id and 7) 结果 from 你的表 tem where n_id<=7
) a where 结果<=21
go
drop table 你的表/*n_id num 结果
----------- ----------- -----------
4 3 18
5 5 15
6 4 10
7 6 6(所影响的行数为 4 行)*/
insert 你的表 values(1 , 5)
insert 你的表 values(2 , 3)
insert 你的表 values(3 , 6 )
insert 你的表 values(4 , 3)
insert 你的表 values(5 , 5)
insert 你的表 values(6 , 4)
insert 你的表 values(7 , 6)
insert 你的表 values(8, 10)select *,(select sum(num) from 你的表 where n_id between tem.n_id and 7) 结果 into #临时表 from 你的表 tem where n_id<=7select * from #临时表 where 结果<=21
union all
select * from #临时表 where n_id=(select min(n_id) from #临时表 where 结果>21)
go
drop table 你的表,#临时表/*
n_id num 结果
----------- ----------- -----------
4 3 18
5 5 15
6 4 10
7 6 6
1 5 32(所影响的行数为 5 行)
*/
--测试:create table 你的表(n_id int,num int)
insert 你的表 values(1 , 5)
insert 你的表 values(2 , 3)
insert 你的表 values(3 , 6 )
insert 你的表 values(4 , 3)
insert 你的表 values(5 , 5)
insert 你的表 values(6 , 4)
insert 你的表 values(7 , 6)
insert 你的表 values(8, 10)select *,(select sum(num) from 你的表 where n_id between tem.n_id and 7) 结果 into #临时表 from 你的表 tem where n_id<=7select * from #临时表 where 结果<=21
union all
select * from #临时表 where n_id=(select min(n_id) from #临时表 where 结果>21)
order by n_id
go
drop table 你的表,#临时表
/*
n_id num 结果
----------- ----------- -----------
1 5 32
4 3 18
5 5 15
6 4 10
7 6 6(所影响的行数为 5 行)
*/
declare @n_id int,@num int
select @n_id=7,@num=21select n_id,num from(
select *,aa=(select sum(num) from t where n_id between a.n_id and @n_id)
from t a where n_id<=@n_id
) a where aa<@num
create table t(n_id int identity(1,1),num int)
insert into t
select 5
union all select 3
union all select 6
union all select 3
union all select 7
union all select 4
union all select 6
union all select 10declare @n_id int,@num int
select @n_id=7,@num=21select n_id,num from(
select *,aa=(select sum(num) from t where n_id between a.n_id and @n_id)
from t a where n_id<=@n_id
) a where aa<@num
go
--删除测试环境
drop table t
/*--测试结果
n_id num
----------- -----------
4 3
5 7
6 4
7 6(所影响的行数为 4 行)
--*/
create table #temp
(
n_id int,
num1 int,
num2 int
)insert into #temp values(1,5,0)
insert into #temp values(2,3,8)
insert into #temp values(3,6,10)
insert into #temp values(4,3,16)
insert into #temp values(5,5,15)
insert into #temp values(6,4,12)
insert into #temp values(7,6,21)
insert into #temp values(8,10,18)
--查询
select n_id,num1,
(select sum(c.num1) from #temp c where c.n_id>=ttt.n_id and c.n_id<=7) as [sum]
from
(select a.n_id n_id,a.num1 num1,
(select num2 from #temp where n_id=7) as parm,
(select sum(b.num1) from #temp b where b.n_id>a.n_id and b.n_id<=7) as [sum]
from #temp a) ttt
where n_id=7 or (n_id<7 and parm>[sum])--删除测试环境
drop table #temp