怎么得出存取款流水余额

如何得出存取款流水余额
比如有一个这样的表
id in out bankID date  
1 100 NULL A 2012-01-03  
2 400 NULL B 2012-01-04  
3 200 NULL A 2012-01-05  
4 NULL 50 B 2012-01-05  

想查询出这样的数据：
id in out bankID date ye
1 100 NULL A 2012-01-03 100
2 400 NULL B 2012-01-04 400
3 200 NULL A 2012-01-05 300
4 NULL 50 B 2012-01-05 350

怎么写才行？

[解决办法]

SQL code

--> 测试数据：[tbl]goif object_id('[tbl]') is not null drop table [tbl]gocreate table [tbl]([name] varchar(1),[date] varchar(5),[num] int)goinsert [tbl]select 'a','1-1号',1 union allselect 'b','1-2号',4 union allselect 'a','1-3号',8 union allselect 'a','1-4号',5 union allselect 'b','1-5号',6 union allselect 'b','1-6号',9;with tas(select ROW_NUMBER()over(partition by nameorder by [date]) as id,*,num  as total from tbl),m as(select id,name,[date],num,total from t where id=1union allselect a.id,a.name,a.[date],a.num,b.total+a.num from t ainner join m b on a.id=b.id+1 and a.name=b.name)select name,[date],num,total from m order by name/*name    date    num    totala    1-3号    8    8a    1-4号    5    13a    1-1号    1    14b    1-2号    4    4b    1-5号    6    10b    1-6号    9    19*/
[解决办法]
SQL code
--> 测试数据：[tbl]if object_id('[tbl]') is not null drop table [tbl]create table [tbl]([id] int,[in] int,[out] int,[bankID] varchar(1),[date] date)insert [tbl]select 1,100,null,'A','2012-01-03' union allselect 2,400,null,'B','2012-01-04' union allselect 3,200,null,'A','2012-01-05' union allselect 4,null,50,'B','2012-01-05';with m as(select ROW_NUMBER()over(PARTITION by [bankID] order by [out],[in])as num,* from tbl),t as(select num,id,[in],[out],[bankID],(case when  [in] is not null then [in] else -[out] end) as totalfrom m where num=1union allselect a.num,a.id,a.[in],a.[out],a.[bankID],(case when  a.[in] is not null then a.[in] else -a.[out] end)+b.total from m ainner join t b on a.num=b.num+1 and a.[bankID]=b.[bankID])select id,[in],[out],bankID,total from t order by [bankID]/*id    in    out    bankID    total1    100    NULL    A    1003    200    NULL    A    3002    400    NULL    B    4004    NULL    50    B    350*/
[解决办法]
SQL code
create table tb(id int,[in] int, [out] int, bankID varchar(10),date datetime)insert into tb values(1 ,100  ,NULL ,'A', '2012-01-03')   insert into tb values(2 ,400  ,NULL ,'B', '2012-01-04')   insert into tb values(3 ,200  ,NULL ,'A', '2012-01-05')insert into tb values(4 ,NULL ,50   ,'B', '2012-01-05')goselect t.* , ye = (select sum(isnull([in],0)-isnull([out],0)) from tb where bankid = t.bankid and date<=t.date) from tb tdrop table tb/*id          in          out         bankID     date                                                   ye          ----------- ----------- ----------- ---------- ------------------------------------------------------ ----------- 1           100         NULL        A          2012-01-03 00:00:00.000                                1002           400         NULL        B          2012-01-04 00:00:00.000                                4003           200         NULL        A          2012-01-05 00:00:00.000                                3004           NULL        50          B          2012-01-05 00:00:00.000                                350（所影响的行数为 4 行）*/