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)) Aleft 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