a表
id username addtime ....
1 aa1 2010-06-06
2 aa2 2010-06-10
3 aa3 2010-06-10
4 aa4 2010-07-10
5 aa5 2010-07-10
.. .... .......
b表
id retime(这个字段是不允许重复的) re
1 2010-06-10 abccccccc
2 2010-07-10 cccccccccddd
-------------------------------------------------------------------------
想得到的结果: 注册日期 注册人数 备注
2010-06-06 1
2010-06-10 2 abccccccc
2010-07-10 2 cccccccccddd这种语句要怎么写?存储过程就不要了,看不懂,也不会用,呃
id username addtime ....
1 aa1 2010-06-06
2 aa2 2010-06-10
3 aa3 2010-06-10
4 aa4 2010-07-10
5 aa5 2010-07-10
.. .... .......
b表
id retime(这个字段是不允许重复的) re
1 2010-06-10 abccccccc
2 2010-07-10 cccccccccddd
-------------------------------------------------------------------------
想得到的结果: 注册日期 注册人数 备注
2010-06-06 1
2010-06-10 2 abccccccc
2010-07-10 2 cccccccccddd这种语句要怎么写?存储过程就不要了,看不懂,也不会用,呃
GROUP BY addtime,re
from a表 a left join b表 b on a.addtime=b.retime
group by a.addtime ,b.备注
drop table #A
go
create table #A(id int,username varchar(5),addtime datetime)
go
insert into #A select '1','aa1','2010/06/06'
union all select '2','aa2','2010/06/10'
union all select '3','aa3','2010/06/10'
union all select '4','aa4','2010/07/10'
union all select '5','aa5','2010/07/10'
go
if object_id('tempdb..#B') is not null
drop table #B
go
create table #B(id int,retime datetime,re text)
go
insert into #B select '1','2010/06/10','abccccccc'
union all select '2','2010/07/10','cccccccccddd'select
注册日期 = a.addtime,
注册人数 = count(*),
备注 = isnull(cast(b.re as varchar(20)),'')
from #A a
left join #B b
on a.addtime = b.retime
group by a.addtime,isnull(cast(b.re as varchar(20)),'')/*
注册日期 注册人数 备注
----------------------- ----------- --------------------
2010-06-06 00:00:00.000 1
2010-06-10 00:00:00.000 2 abccccccc
2010-07-10 00:00:00.000 2 cccccccccddd(3 row(s) affected)
*/