table1 table2
id firstTime usid lastTime
1 2007-5-1 1 2007-5-2 00:5:01
2 2007-5-1 2 2007-5-2 05:06:12
3 2007-5-2 3 2007-5-6 12:01:15
4 2007-5-3 4 2007-6-2 15:11:12
5 2007-5-3 5 2007-5-8 00:00:05
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-5-6 0 0
2007-5-7 0 0
2007-5-8 0 0
...........
...........
2007-6-2 2
感激不尽....
id firstTime usid lastTime
1 2007-5-1 1 2007-5-2 00:5:01
2 2007-5-1 2 2007-5-2 05:06:12
3 2007-5-2 3 2007-5-6 12:01:15
4 2007-5-3 4 2007-6-2 15:11:12
5 2007-5-3 5 2007-5-8 00:00:05
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-5-6 0 0
2007-5-7 0 0
2007-5-8 0 0
...........
...........
2007-6-2 2
感激不尽....
select * from (
(select firsttime,count(firsttime) as count from table1 group by firsttime)
as a
left join (select lastTime ,count(lastTime ) as count from table2 group by lastTime ) as b
on a.firsttime=b.lasttime
) as temp
select t1.firstTime as time, count(t.firstTime) as 'count(firstTime)' ,count(t2.lastTime) as 'count
(lastTime)'
from table1 as t1,table2 as t2
group by t1.firstTime;