如何得出存取款流水余额

Question

比如有一个这样的表
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怎么写才行？

Answer 1

--> 测试数据：[tbl]
go
if object_id('[tbl]') is not null 
drop table [tbl]
go
create table [tbl](
[name] varchar(1),
[date] varchar(5),
[num] int
)
go
insert [tbl]
select 'a','1-1号',1 union all
select 'b','1-2号',4 union all
select 'a','1-3号',8 union all
select 'a','1-4号',5 union all
select 'b','1-5号',6 union all
select 'b','1-6号',9;with t
as(
select ROW_NUMBER()over(partition by name
order by [date]) as id,
*,num  as total from tbl
),
m as(
select id,name,[date],num,total from t where id=1
union all
select a.id,a.name,a.[date],a.num,b.total+a.num from t a
inner 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 total
a 1-3号 8 8
a 1-4号 5 13
a 1-1号 1 14
b 1-2号 4 4
b 1-5号 6 10
b 1-6号 9 19
*/

Answer 2

--> 测试数据：[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 all
select 2,400,null,'B','2012-01-04' union all
select 3,200,null,'A','2012-01-05' union all
select 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 total
from m where num=1
union all
select 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 a
inner 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 total
1 100 NULL A 100
3 200 NULL A 300
2 400 NULL B 400
4 NULL 50 B 350
*/

Answer 3

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                                100
2           400         NULL        B          2012-01-04 00:00:00.000                                400
3           200         NULL        A          2012-01-05 00:00:00.000                                300
4           NULL        50          B          2012-01-05 00:00:00.000                                350（所影响的行数为 4 行）
*/