declare @empno varchar(20),@num float,@sql float,@sqll varchar(20) declare aa cursor for select empno,num from Temp_empno order by empno open aa fetch next from aa into @empno,@num while @@fetch_status=0 begin set @sql=@sql+@num if @sql=10000 begin set @sqll=@empno end fetch next from aa into @empno,@num end close aa deallocate aa select * from Temp_empno where empno<=@empno用游标可以实现,但是速度就会有点慢,所以还是建议用循环语句比较方便和实用啊
思路: create table #t(编码 varchar(10), 数量 int) insert into #t select '001', 5 union all select '002', 6 union all select '003', 10 union all select '004', 11 union all select '005', 3 union all select '006', 113 --- select 编码,数量 from #t a where (select sum(数量) from #t b where a.编码>=b.编码)<10000
create table #t(编码 varchar(10), 数量 int) insert into #t select '001', 9997 union all select '002', 1 union all select '003', 1 union all select '004', 1 union all select '005', 3 select 编码,数量 from #t a where (select sum(数量) from #t b where a.编码>=b.编码)<10000 编码 数量 ---------- ----------- 001 9997 002 1 003 1 drop table #t
--> 测试数据:# if object_id('tempdb.dbo.#') is not null drop table # create table #(编码 varchar(8), 数量 int) insert into # select '001', 5 union all select '002', 6 union all select '003', 10 union all select '004', 11 union all select '005', 3 union all select '006', 113--> 没那么大的数,假设满足30的: ;with cte as ( select *, s=(select sum(数量) from # where 编码<=t.编码) from # as t ) select * from cte where 编码 <= (select top 1 编码 from cte where s>=30 order by 编码)/* 编码 数量 s -------- ----------- ----------- 001 5 5 002 6 11 003 10 21 004 11 32 */
declare @empno varchar(20),@num float,@sql float,@sqll varchar(20) declare aa cursor for
select empno,num from Temp_empno order by empno
open aa
fetch next from aa into @empno,@num
while @@fetch_status=0
begin
set @sql=@sql+@num
if @sql=10000
begin
set @sqll=@empno
end
fetch next from aa into @empno,@num
end
close aa deallocate aa select * from Temp_empno where empno<=@empno用游标可以实现,但是速度就会有点慢,所以还是建议用循环语句比较方便和实用啊
insert into #t
select '001', 5 union all
select '002', 6 union all
select '003', 10 union all
select '004', 11 union all
select '005', 3 union all
select '006', 113
---
select 编码,数量 from #t a
where (select sum(数量) from #t b where a.编码>=b.编码)<10000
insert into #t
select '001', 9997 union all
select '002', 1 union all
select '003', 1 union all
select '004', 1 union all
select '005', 3 select 编码,数量 from #t a
where (select sum(数量) from #t b where a.编码>=b.编码)<10000
编码 数量
---------- -----------
001 9997
002 1
003 1
drop table #t
if object_id('tempdb.dbo.#') is not null drop table #
create table #(编码 varchar(8), 数量 int)
insert into #
select '001', 5 union all
select '002', 6 union all
select '003', 10 union all
select '004', 11 union all
select '005', 3 union all
select '006', 113--> 没那么大的数,假设满足30的:
;with cte as
(
select *, s=(select sum(数量) from # where 编码<=t.编码) from # as t
)
select * from cte where 编码 <= (select top 1 编码 from cte where s>=30 order by 编码)/*
编码 数量 s
-------- ----------- -----------
001 5 5
002 6 11
003 10 21
004 11 32
*/