日期:2014-05-17 浏览次数:20425 次
create table #ta(AccountNo varchar(100),name varchar(10),regtime datetime)
insert into #ta
select 'wwwwwwwww','pp','2013-06-01'
union all select 'wwwwwwwww','fb','2013-06-15'
union all select 'wwwwwwwww','tos','2013-06-25'
create table #tb(AccountNo varchar(100),addMoney money,addtime datetime)
insert into #tb
select 'wwwwwwwww',10,'2013-06-03'
union all select 'wwwwwwwww',20,'2013-06-04'
union all select 'wwwwwwwww',20,'2013-06-16'
union all select 'wwwwwwwww',50,'2013-06-26'
union all select 'wwwwwwwww',33,'2013-06-29'
select * from #ta
select * from #tb
select a.name,amt=(select sum(addMoney) from #tb b
where a.AccountNo=b.AccountNo and b.addtime between a.regtime and isnull((select top 1 regtime from #ta c where c.regtime>a.regtime),getdate()))
from #ta a
drop table #ta,#tb
结果:
/*
name amt
-----------------------
pp 30.0000
fb 20.0000
tos 83.0000
*/