当前位置: 代码迷 >> ASP.NET >> SQL语句.感激不尽.快没分了
  详细解决方案

SQL语句.感激不尽.快没分了

热度:2959   发布时间:2013-02-25 00:00:00.0
求一个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)) 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
  相关解决方案