如何得出存取款流水余额

[mmfeng]

比如有一个这样的表
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

怎么写才行？

[mmfeng]

其中A、B是指不同的银行

[开着拖拉机泡妞]

--> 测试数据：[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
*/

[开着拖拉机泡妞]

--> 测试数据：[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
*/

[dawugui]

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')
go

select t.* , ye = (select sum(isnull([in],0)-isnull([out],0)) from tb where bankid = t.bankid and date<=t.date) from tb t

drop 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 行）
*/

[mmfeng]

太谢谢了，我做了半天没有做出，楼上的几位就这么一会就出来了，太强了。