34,594
社区成员
发帖
与我相关
我的任务
分享
declare @i int,@Sum int,@x int
set @x=300
set @i=1
set @Sum=0
select @Sum=sum(金额) from mytb where id<=@i
while @Sum<@x
begin
set @i=@i+1
select @Sum=sum(金额) from mytb where id<@i
end
select * from mytb where id<@i-1
----------------------------------------------------------------
-- Author :SQL77(只为思齐老)
-- Date :2010-01-27 14:27:50
-- Version:
-- Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
-- Aug 6 2000 00:57:48
-- Copyright (c) 1988-2000 Microsoft Corporation
-- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:#tb
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb([id] int,[a] int,[b] varchar(1))
insert #tb
select 1,100,'a' union all
select 2,80,'b' union all
select 3,200,'c' union all
select 4,500,'d' union all
select 5,100,'e' union all
select 6,300,'f'
--------------开始查询--------------------------
declare @x int
set @x=512
select * from #tb where id in(
select id from #tb t where (select sum(a) from #tb where id<=t.id)<=512)
----------------结果----------------------------
/*
(所影响的行数为 6 行)
id a b
----------- ----------- ----
1 100 a
2 80 b
3 200 c
(所影响的行数为 3 行)
*/
select * from [Table] a where (select sum(a) from [Table] where id<=a.id)<x
select * from TableName where sum(a)<=512