求一个SQL语句..感激不尽..快没分了
table1 table2
id firstTime usid lastTime
1 2007-5-1 1 2007-5-2
2 2007-5-1 2 2007-5-2
3 2007-5-2 3 2007-5-6
4 2007-5-3 4 2007-6-2
5 2007-5-3 5 2007-5-8
6 2007-5-4
7 2007-6-1
8 2007-6-2
9 2007-6-2

我希望得到的结果是这样的
time count(firstTime) count(lastTime)
2007-5-1 2 0
2007-5-2 1 2
2007-5-3 2 0
2007-5-4 1 0
2007-5-5 0 0
2007-6-2 2 1



怎么写哈..感激不尽....  
 


------解决方案--------------------
select a.firsttime as time,a.f as firstCount, b.l as lastCount from
(
select firsttime,count(firstTime) as f form table1 group by firsttime
) a,
(
select lasttime,count(lastTime) as l form table2 group by
) b
where a.firsttime=b.lastTime(+)
没有测试!看行不!
------解决方案--------------------
--通过测试

SQL code

select A.time,A.firstTime,isnull(B.lastTime,0) from 
(select distinct convert(varchar(10),firstTime,120) as 'time',count(firstTime) as 'firstTime' from tt1 group by convert(varchar(10),firstTime,120)) A
left join 
(select distinct convert(varchar(10),lastTime,120) as 'time',count(lastTime) as 'lastTime' from tt2 group by convert(varchar(10),lastTime,120)) B
 on A.time = B.time