请教SQL游标 进行逐行累加数值的问题

[临窗飞飞]

在做游标的时候遇到了一个问题，请教下大家
表#temp

Docentry QUANTITY QUANTITY1
3979 3
3981 1
3980 1
3978 2


想通过游标遍历成
Docentry QUANTITY QUANTITY1
3979 3 3
3981 1 4
3980 1 5
3978 2 7

游标部分

定义变量部分
declare @Quantity      numeric(19,6)
       ,@Docentry   int    
       ,@Quantity1     numeric(19,6)  

declare GetSCSH cursor for select Docentry,QUANTITY,QUANTITY1 from #temp
  open GetSCSH
    fetch next from GetSCSH into Docentry,Quantity,@Quantity1
      while @@fetch_status=0
        begin
          if(@Quantity<>0)
	   begin
             update #temp set Quantity1=@Quantity1+@Quantity where Docentry=@Docentry 
            end
          fetch next from GetSCSH into @Docentry,@Quantity,@Quantity1
        end
   close GetSCSH
deallocate GetSCSH

SELEVT * FROM #temp

结果却变成了下面这种错误的情况，请大家指教
Docentry QUANTITY QUANTITY1
3979 3 6
3981 1 2
3980 1 2
3978 2 4

[--小F--]

为什么要用游标？

直接
select
 Docentry, QUANTITY ,
 (select sum(QUANTITY) from tb where Docentry<=t.Docentry) 
from
  tb t

[天-笑]

select Docentry ,QUANTITY ,(select sum(QUANTITY) from #temp b where b.Docentry<=a.Docentry)  as  QUANTITY1
from #temp a 

[临窗飞飞]

[Quote=引用 1 楼 的回复:]
SQL code

为什么要用游标？

直接
select
Docentry, QUANTITY ,
(select sum(QUANTITY) from tb where Docentry<=t.Docentry)
from
tb t
[/Quote]

因为游标不会动表里面 行的顺序

这里不需要按Docentry排序！

所以用游标逐行遍历！

[--小F--]

[Quote=引用 3 楼 的回复:]
引用 1 楼 的回复:
SQL code

为什么要用游标？

直接
select
Docentry, QUANTITY ,
(select sum(QUANTITY) from tb where Docentry<=t.Docentry)
from
tb t


因为游标不会动表里面 行的顺序

这里不需要按Docentry排序！

所以用游标逐行遍历！
[/Quote]

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2012-05-16 10:25:32
-- Version:
--      Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) 
--	Apr 22 2011 11:57:00 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([Docentry] int,[QUANTITY] int,[QUANTITY1] sql_variant)
insert [tb]
select 3979,3,null union all
select 3981,1,null union all
select 3980,1,null union all
select 3978,2,null
--------------开始查询--------------------------
;with f as
(
select px=row_number()over(order by getdate()),* from tb
)

select
Docentry, QUANTITY ,
(select sum(QUANTITY) from f where px<=t.px)  as QUANTITY1
from
 f t
----------------结果----------------------------
/* Docentry    QUANTITY    QUANTITY1
----------- ----------- -----------
3979        3           3
3981        1           4
3980        1           5
3978        2           7

(4 行受影响)

*/

[--小F--]

加个自增列 也可以不用游标 游标效率低了点。

[唐诗三百首]

try this,

declare @Quantity numeric(19,6)
       ,@Docentry int    
       ,@Quantity1 numeric(19,6)=0

declare GetSCSH cursor for select Docentry,QUANTITY from #temp
open GetSCSH
fetch next from GetSCSH into @Docentry,@Quantity
      while @@fetch_status=0
      begin
          if(@Quantity<>0)
           begin
             update #temp set Quantity1=@Quantity1+@Quantity where Docentry=@Docentry 
             select @Quantity1=@Quantity1+@Quantity
           end
          fetch next from GetSCSH into @Docentry,@Quantity
      end
close GetSCSH
deallocate GetSCSH

SELECT * FROM #temp

[Felixzhaowenzhong]

create table tb (Docentry bigint primary key,QUANTITY int)
insert tb select 
3979 ,3 union 
select 3981, 1 union 
select 3980 ,1 union 
select 3978, 2 


select * from tb 
/*
Docentry	QUANTITY
3978	2
3979	3
3980	1
3981	1
*/
select Docentry ,QUANTITY ,(select sum(QUANTITY) from tb  where Docentry<=a.Docentry)  as  QUANTITY1
from tb a 
/*
Docentry	QUANTITY	QUANTITY1
3978	2	2
3979	3	5
3980	1	6
3981	1	7
*/

drop table tb

[--小F--]

还有你游标的语法有错误 自己去查查联机丛书。

[临窗飞飞]

[Quote=引用 5 楼 的回复:]
加个自增列 也可以不用游标 游标效率低了点。
[/Quote]


我看到这个
Docentry<=t.Docentry
也想到了，呵呵

价格自定义列ID，输入1 2 3 4
就解决了！！！

[唐诗三百首]

declare @Quantity numeric(19,6)
       ,@Docentry int    
       ,@Quantity1 numeric(19,6)=0

declare GetSCSH cursor for select Docentry,QUANTITY from #temp
open GetSCSH
fetch next from GetSCSH into @Docentry,@Quantity
      while @@fetch_status=0
      begin
          if(@Quantity<>0)
           begin
             update #temp set Quantity1=@Quantity1+@Quantity where Docentry=@Docentry 
             select @Quantity1=@Quantity1+@Quantity
           end
          fetch next from GetSCSH into @Docentry,@Quantity
      end
close GetSCSH
deallocate GetSCSH

SELECT * FROM #temp

[孤独加百列]

declare @Quantity      numeric(19,6)
       ,@Docentry      int    
       ,@Quantity1     numeric(19,6)  
SET @Quantity1 = 0
declare GetSCSH cursor for select Docentry,QUANTITY from #temp
  open GetSCSH
    fetch next from GetSCSH into @Docentry,@Quantity
      while @@fetch_status=0
        begin
          if(@Quantity<>0)
          begin
			 SET @Quantity1 = @Quantity1 + @Quantity
             update #temp set Quantity1 = @Quantity1 where Docentry=@Docentry 
          end
          fetch next from GetSCSH into @Docentry,@Quantity
        end
   close GetSCSH
deallocate GetSCSH

[hbdn520]

我想了解下 Docentry<=t.Docentry 这个是什么意思，有知道的可以告诉下我吗

[百年树人]

declare @tmp int
update #temp
  set @tmp=isnull(@tmp,0)+QUANTITY,QUANTITY1=@tmp

[临窗飞飞]

请教下前辈，这个基础上在相同Docentry下分行排序

比如增加把3979 拆分成2行 ，有个内部ID1按大小排序
ID Docentry ID1 QUANTITY
1 3979 1 2
1 3979 2 1
2 3981 1 1
3 3980 1 1
4 3978 1 2

怎样在原来语句（
select
Docentry, QUANTITY ,
(select sum(QUANTITY) from tb where Docentry<=t.Docentry)
from
tb t


）的基础上修改输出为


ID Docentry ID1 QUANTITY QUANTITY1
1 3979 1 2 2
1 3979 2 1 3
2 3981 1 1 4
3 3980 1 1 5
4 3978 1 2 7


还请前辈赐教！！