关于SQL循环累加的问题

[hndxwqt]

有这样的原始表：
X1 X2
-----------------------
2 453
2 120
2 101
2 89
3 111
3 421
3 219
-----------------------
根据X1字段进行累加，我要循环累加后变成下面的表
X1 X2
-----------------------
2 453
2 573
2 674
2 763
3 111
3 532
3 751
-----------------------
好象要用到游标，但不是很会用，没办法就到这里来请教各位朋友了。谢谢。

[hellowork]

declare @t table(X1 int, X2 int)
insert @t
select 2, 453 union all
select 2, 120 union all
select 2, 101 union all
select 2, 89 union all
select 3, 111 union all
select 3, 421 union all
select 3, 219

----更新
declare @num int,@x1 int
set @num = 0
update @t set
@num = case when @x1 = X1 then @num + X2 else X2 end,
@x1 = X1,
X2 = @num

----查看
select * from @t

/*结果
X1 X2
-----------------------
2 453
2 573
2 674
2 763
3 111
3 532
3 751
*/

[昵称被占用了]

declare @X1 int
declare @X2 int

select * into #
from 原始表
order by x1

update #
set @x2=case when @x1 is null or @x1<>x1 then x2 else @x2+x2 end,
@x1=x1,
x2=@x2

select * from #

drop table #

[迷失的空间]

这样可以吗？

[fa_ge]

create table t( X1 int, X2 int)
insert t
select 2, 453 union all
select 2, 120 union all
select 2, 101 union all
select 2, 89 union all
select 3,

ALTER TABLE t
ADD id int identity(1,1)

111 union all
select 3, 421 union all
select 3, 219


select X1,X2, X3 =(select SUM(X2) from t where X1=a.X1 AND id>=a.id group by a.X1)
FROM t a
ORDER BY a.X1,a.X2



X1 X2 X3
----------- ----------- -----------
2 89 89
2 101 190
2 120 310
2 453 763
3 111 751
3 219 219
3 421 640

(7 row(s) affected)

[sp4]

不需要游标
hellowork(一两清风) 和 Haiwer(海阔天空) 就很好

Haiwer(海阔天空) 的更完整准确

[stswordman]

强

[hlf1989]

select   *,id=identity(1,1)   into   #1 
from   原始表 

select   x1,x2,(select sum(x2) from # a where a.x1=#1.X1 and a.id>=#1.id)   
from   #1 

drop   table   #1 

[yibey]

create  table  #t   (X1   int,               X2   int)
insert   #t
select   2,                 453   union   all
select   2,                 120   union   all
select   2,                 101   union   all
select   2,                 89   union   all
select   3,                 111   union   all
select   3,                 421   union   all
select   3,                 219 

with cte as
(
select *,ID=row_number() over(partition by X1 order by X1) from #t
)
select X1,X2=(select sum(X2) from cte a where a.X1=b.X1 AND a.ID<=b.ID) FROM cte b
/*
X1          X2
----------- -----------
2           453
2           573
2           674
2           763
3           111
3           532
3           751

(7 row(s) affected)

[cdcorg]

问题求解：在MYSQL中，对表的一列数字进行分组循环累加





问题同

http://topic.csdn.net/u/20070717/07/1a2b9317-eec7-453d-8698-044237e47086.html

有这样的原始表：
X1 X2
-----------------------
2 453
2 120
2 101
2 89
3 111
3 421
3 219
-----------------------

根据X1字段进行累加，我要循环累加后变成下面的表
X1 X2
-----------------------
2 453
2 573
2 674
2 763
3 111
3 532
3 751
-----------------------


/////////////////////////////////////////////////
原贴2楼Haiwer的解决方案：


declare @X1 int
declare @X2 int

select * into #
from 原始表
order by x1

update #
set @x2=case when @x1 is null or @x1 <> x1 then x2 else @x2+x2 end,
@x1=x1,
x2=@x2

select * from #

drop table #
/////////////////////////////////////////////////

这段代码适用于MS SQL，但MYSQL中的UPDATE貌似不支持set @变量名=字段名这种写法，求各位帮忙（方法可用游标或不用游标）。






省时省力代码：

CREATE TABLE temptb(
`x1` INT NULL NULL ,
`x2` INT NULL NULL );


INSERT INTO temptb VALUES(2,453);
INSERT INTO temptb VALUES(2,120);
INSERT INTO temptb VALUES(2,101);
INSERT INTO temptb VALUES(2,89);
INSERT INTO temptb VALUES(3,111);
INSERT INTO temptb VALUES(3,421);
INSERT INTO temptb VALUES(3,219);

[ly745455]

学习了。

[叶子]

declare @table table (X1 int,X2 int)
insert into @table
select 2,453 union all
select 2,120 union all
select 2,101 union all
select 2,89 union all
select 3,111 union all
select 3,421 union all
select 3,219

;with maco as
(
select *,row_number() over (partition by X1 ORDER BY X1) AS RID from @table 
)
select X1,(select sum(X2) from maco 
where X1=t.X1 AND RID<=t.RID) AS X2 from maco t
/*
X1          X2
----------- -----------
2           453
2           573
2           674
2           763
3           111
3           532
3           751
*/

[cdcorg]

谢谢~

[Quote=引用 2 楼 haiwer 的回复:]

declare @X1 int
declare @X2 int

select * into #
from 原始表
order by x1

update #
set @x2=case when @x1 is null or @x1 <> x1 then x2 else ……
[/Quote]

[cdcorg]

[Quote=引用 12 楼 maco_wang 的回复:]

SQL code

declare @table table (X1 int,X2 int)
insert into @table
select 2,453 union all
select 2,120 union all
select 2,101 union all
select 2,89 union all
select 3,111 union all
select 3,421 union……
[/Quote]

可是这段代码同9楼，MYSQL并不支持 ROW_NUMBER()
引用：http://topic.csdn.net/u/20090504/15/bfbc217b-4ae4-4f36-9810-21de077fb034.html

能否用变量和条件语句的方法，改写下面的代码？

update   # 
        set   @x2=case   when   @x1   is   null   or   @x1 <> x1   then   x2   else   @x2+x2   end, 
                @x1=x1, 
                x2=@x2 

[jiangzhong610]

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

declare @table table (X1 int,X2 int)
insert into @table
select 2,453 union all
select 2,120 union all
select 2,101 union all
select 2,89 union all
select 3,111 union all
select 3,42……
[/Quote] 这段SQL我怎么执行不了啊 是在什么情况下啊 我用的是SQL 2005

[cdcorg]

问题已移到MYSQL区：

http://topic.csdn.net/u/20110525/18/268166de-5df8-4151-a007-c66874b960fd.html