有这样的原始表:
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
-----------------------
好象要用到游标,但不是很会用,没办法就到这里来请教各位朋友了。谢谢。
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
-----------------------
好象要用到游标,但不是很会用,没办法就到这里来请教各位朋友了。谢谢。
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 @X2 intselect * into #
from 原始表
order by x1update #
set @x2=case when @x1 is null or @x1<>x1 then x2 else @x2+x2 end,
@x1=x1,
x2=@x2select * from #drop table #
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.X2X1 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)
hellowork(一两清风) 和 Haiwer(海阔天空) 就很好Haiwer(海阔天空) 的更完整准确
from 原始表 select x1,x2,(select sum(x2) from # a where a.x1=#1.X1 and a.id>=#1.id)
from #1 drop table #1
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)
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);
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
*/
可是这段代码同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