关于两个表的查询，帮忙看下
帮忙看看这两个表，
一个帐号 6.1号从pp这个媒体注册进来，然后6.15号又从fb注册进来，然后6.25号又从tos注册进来
那么这个帐号的充值
6.1号到6.15号的充值算在pp里面
6.15号到6.25号的充值算在 fb里面
6.25之后的充值算在tos里面
要最后得出的结果为：
pp 30
fb 20
tos 83
[解决办法]
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
-----------------------
pp30.0000
fb20.0000
tos83.0000
*/
