求SQL数据统计结果

[willdavis]

我想要这样的效果
交易值 结存值
100 （期初数）
50 150
90 240
300 540
80 620

请问在SQL中如何实现？

[zccmy22]

说的不是很清楚.

[liangCK]

没看懂.

[晓风残月0110]

原始数据，
要不哪来的数据

[willdavis]

可能是我讲的不太清楚

已有数据 计算结果
50 50
90 140
300 440
80 520
本次的计算结果等于上次的计算结果加本次的已有数据
我想我应该说明白了吧

[tthxqttzln]

表A
id a1 a2
1 50
2 90
3 300
4 80

declare @id int,@a1 int,@a2 int
DECLARE A_cursor CURSOR FOR
SELECT id,isnull(a1,0),isnull(a2,0) FROM A ORDER BY id

OPEN A_cursor
set @prea2=0
FETCH NEXT FROM A_cursor INTO @id, @a1,@a2

WHILE @@FETCH_STATUS = 0
BEGIN
update A set a2=a1+@prea2 where id=@id
set @prea2=@prea2+@a1
end

FETCH NEXT FROM A_cursor INTO @id, @a1,@a2

select * from A

得出
id a1 a2
1 50 50
2 90 140
3 300 440
4 80 520

[nzperfect]

--> 测试数据: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (id int,col2 int)
insert into #T
select 1,50 union all
select 2,90 union all
select 3,300 union all
select 4,80

select id,col2,(select sum(col2) from #t where id<=a.id) as col3 from #T as a

/*
-----------------
1	50	50
2	90	140
3	300	440
4	80	520
*/

[hery2002]

[Quote=引用楼主 willdavis 的帖子:]
我想要这样的效果
交易值 结存值
100 （期初数）
50 150
90 240
300 540
80 620

请问在SQL中如何实现？
[/Quote]
需要id或者类似的增长列,
如果没有的话,就需要添加临时表和自增长列来完成.如:

/*
交易日期  交易值      结存值 
2008-01-01   0           100 
2008-01-02  50          150 
2008-01-03 90          240 
2008-01-04 300        540 
2008-01-05 80          620
*/

-->生成测试数据
 
declare @tb table([交易日期] Datetime,[交易值] int,[结存值] int)
Insert @tb
select '2008-01-01',N'0',100 union all
select '2008-01-02',N'50',150 union all
select '2008-01-03',N'90',240 union all
select '2008-01-04',N'300',540 union all
select '2008-01-05',N'80',620
Select *,
      (select [结存值] from @tb where [交易日期] = (select min([交易日期]) from @tb) ) +
      (select sum([交易值]) from @tb where [交易日期] <= t.[交易日期])  as [新的结存值]
from @tb t
/*
(5 row(s) affected)
交易日期                    交易值         结存值         新的结存值
----------------------- ----------- ----------- -----------
2008-01-01 00:00:00.000 0           100         100
2008-01-02 00:00:00.000 50          150         150
2008-01-03 00:00:00.000 90          240         240
2008-01-04 00:00:00.000 300         540         540
2008-01-05 00:00:00.000 80          620         620

(5 row(s) affected)
*/

[Andy-W]

--> --> (Andy)生成测试数据 2008-06-19 21:29:12
Set Nocount On
declare @1 table(col int)
Insert @1
select 50 union all
select 90 union all
select 300 union all
select 80

;With
t1 As (Select id=Convert(int,1),col from @1),
t2 As (Select id=row_number() Over(Order By id),col From t1),
t3 As
(
Select id,col,value=Convert(int,col) From t2 Where id=1
Union All
Select a.id,a.col,value=Convert(int,b.value+a.col) From t2 a ,t3 b Where a.id=b.id+1
)
Select * From t3


/*
id col value
-------------------- ----------- -----------
1 50 50
2 90 140
3 300 440
4 80 520


*/

[cxmcxm]

交易值 结存值
100 （期初数）
50 150
90 240
300 540
80 620

--先创建一临时表保存交易值与交易顺序
declare @tmp (id int identity(1,1),
             jyz numeric(18,2),--交易值
               jcz numeric(18,2) default 0 --结存值
               )
--插入交易值
insert into @tmp (jyz) select 50
union select 90
union select  300
union select 80

--计算期初为0的结存值
update a
set jcz=(select sum(jyz) from @tmp where id<=a.id) --计算前面交易值的和
from @tmp a
--将@tmp 插入带期初数的表,将@tmp中结存值加上期初数即为需要的结存值

[willdavis]

谢谢各位指点.结贴给分.