请教sql

SQL code

 
 	create table #A 
 	( 
 		 id int, 
 		 buyid int, 
 		 sellid int, 
 		 [time] datetime 
 	) 
 	insert into #A values (1,1,2,'2007-1-1') 
 	insert into #A values (2,3,4,'2007-2-1') 
 	insert into #A values (3,5,2,'2007-3-1') 
 	insert into #A values (4,6,3,'2007-3-3') 
 	insert into #A values (5,1,3,'2007-4-2') 
 	 
 	create table #B 
 	( 
 		id int, 
 		[name] nvarchar(20) 
  	) 
  	insert into #B values (1,'A') 
  	insert into #B values (2,'B') 
  	insert into #B values (3,'C') 
  	insert into #B values (4,'D') 
  	insert into #B values (5,'E') 
  	insert into #B values (6,'F') 
  	 
  

现在sql有两表，关联，A表的buyid,sellid 关联B表的id.
我想得出下面答案：
[姓名] [买卖] [次数]
---------------------------
A buy 2
B sell 2
C buy 3
D sell 1
E buy 1
F buy 1
后面的次数最好可以按A表的时间计算，就是输入两个时间。计算在此时间中的次数。
尽可能考虑速度，数据量可能会很大。谢谢


------解决方案--------------------
declare @time1 datetime ,
@time2 datetime
select @time1='2006-11-26',@time2='2007-11-26'
select * from 
(select [name],b='buy',time1 =count(*) from #A,#B where buyid=#B.id and [time] between @time1 and @time2
group by #B.id,[name])a
union all select * from 
(select [name],b='sell',time1=count(*) from #A,#B where sellid=#B.id and [time] between @time1 and @time2
group by #B.id,[name])b
order by [name]