关于一个表中列数据的相关计算_Sql Server

求助：关于一个表中列数据的相关计算
有一个表 DATE ID CHANGE AMOUNT
2010 1 100 1000
2010 1 -200 1000
2010 1 200 1000
2011 1 200 1100
2011 1 400 1100

现在的AMOUNT 是不动的余额。
我想让它加入CHANGE 的变化做出下面的效果
DATE ID CHANGE AMOUNT
2010 1 100 1100
2010 1 -200 900
2010 1 200 1100
2011 1 200 1300
2011 1 400 1700

如何做？

------解决方案--------------------

SQL code

declare @test table(DATE int, ID int, CHANGE int, AMOUNT int)insert into @testselect 2010, 1, 100, 1000 union allselect 2010, 1, -200, 1000 union allselect 2010, 1, 200, 1000 union allselect 2011, 1, 200, 1100 union allselect 2011, 1, 400, 1100;with cte as(    select row_number() over(partition by ID order by getdate()) rn,* from @test)select DATE,ID,CHANGE,AMOUT=(select sum(CHANGE) from cte where t.ID=ID and t.rn>=rn)+1000 from cte t/*DATE        ID          CHANGE      AMOUT----------- ----------- ----------- -----------2010        1           100         11002010        1           -200        9002010        1           200         11002011        1           200         13002011        1           400         1700*/
------解决方案--------------------
SQL codeselect date,id,change,amount+change as amount from tablename
------解决方案--------------------
SQL code--> 测试数据：[tb]IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]GO CREATE TABLE [tb]([DATE] INT,[ID] INT,[CHANGE] INT,[AMOUNT] INT)INSERT [tb]SELECT 2010,1,100,1000 UNION ALLSELECT 2010,1,-200,1000 UNION ALLSELECT 2010,1,200,1000 UNION ALLSELECT 2011,1,200,1100 UNION ALLSELECT 2011,1,400,1100--------------开始查询--------------------------;WITH cte AS (SELECT *, row_id=ROW_NUMBER()OVER(PARTITION BY [DATE] ORDER BY GETDATE()) FROM [tb])SELECT *,[AMOUNT]+(SELECT SUM([CHANGE]) FROM cte WHERE [DATE]=t.[DATE] AND row_id<=t.row_id)  FROM cte  AS t----------------结果----------------------------/* DATE    ID    CHANGE    AMOUNT    row_id    (无列名)2010    1    100    1000    1    11002010    1    -200    1000    2    9002010    1    200    1000    3    11002011    1    200    1100    1    13002011    1    400    1100    2    1700*/--> 测试数据：[tb]IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]GO CREATE TABLE [tb]([DATE] INT,[ID] INT,[CHANGE] INT,[AMOUNT] INT)INSERT [tb]SELECT 2010,1,100,1000 UNION ALLSELECT 2010,1,-200,1000 UNION ALLSELECT 2010,1,200,1000 UNION ALLSELECT 2011,1,200,1100 UNION ALLSELECT 2011,1,400,1100--------------开始查询--------------------------;WITH cte AS (SELECT *, row_id=ROW_NUMBER()OVER(PARTITION BY [DATE] ORDER BY GETDATE()) FROM [tb])SELECT *,[AMOUNT]+(SELECT SUM([CHANGE]) FROM cte WHERE [DATE]=t.[DATE] AND row_id<=t.row_id)  FROM cte  AS t----------------结果----------------------------/* DATE    ID    CHANGE    AMOUNT    row_id    (无列名)2010    1    100    1000    1    11002010    1    -200    1000    2    9002010    1    200    1000    3    11002011    1    200    1100    1    13002011    1    400    1100    2    1700*/