假设有表T,内有两个字段:ID和Num
ID是自增主键,Num里是float型的数字
对任意的数字N,我要取出以ID排序的,且sum(Num)<N的连续记录
SQL语句怎么写?ID NUM
1 1
2 1
3 1
4 1
5 10
6 1
7 1
8 1
9 1
10 1假设N为9 则我想要的记录是ID1-4的 而不是1-4,6-9
ID是自增主键,Num里是float型的数字
对任意的数字N,我要取出以ID排序的,且sum(Num)<N的连续记录
SQL语句怎么写?ID NUM
1 1
2 1
3 1
4 1
5 10
6 1
7 1
8 1
9 1
10 1假设N为9 则我想要的记录是ID1-4的 而不是1-4,6-9
declare @tb table([ID] int,[NUM] int)
insert @tb
select 1,1 union all
select 2,1 union all
select 3,1 union all
select 4,1 union all
select 5,10 union all
select 6,1 union all
select 7,1 union all
select 8,1 union all
select 9,1 union all
select 10,1DECLARE @N int;
SET @N = 9;SELECT *
FROM @tb AS A
WHERE (SELECT SUM(num) FROM @tb WHERE id<=A.id) < @N
insert into tb values(1 ,1)
insert into tb values(2 ,1)
insert into tb values(3 ,1)
insert into tb values(4 ,1)
insert into tb values(5 ,10)
insert into tb values(6 ,1)
insert into tb values(7 ,1)
insert into tb values(8 ,1)
insert into tb values(9 ,1)
insert into tb values(10, 1)
godeclare @n as int
set @n = 9select id , num from
(
select t.* , n = (select sum(num) from tb where id <= t.id) from tb t
) m
where n <= @n/*
id num
----------- -----------
1 1
2 1
3 1
4 1(所影响的行数为 4 行)
*/drop table tb
insert into tb values(1 ,1)
insert into tb values(2 ,1)
insert into tb values(3 ,1)
insert into tb values(4 ,1)
insert into tb values(5 ,10)
insert into tb values(6 ,1)
insert into tb values(7 ,1)
insert into tb values(8 ,1)
insert into tb values(9 ,1)
insert into tb values(10, 1)
godeclare @n as int
set @n = 9select ltrim(min(id)) + '-' + ltrim(max(id)) from
(
select t.* , n = (select sum(num) from tb where id <= t.id) from tb t
) m
where n <= @n/*
-------------------------
1-4(所影响的行数为 1 行)
*/drop table tb