sql统计 统计首页访问量

[hongbowu]

id userid addtime
1 a 2008-8-8 11:01
2 b 2008-8-8 12:01
3 c 2008-8-8 13:01
4 a 2008-8-8 14:01
5 a 2008-8-8 15:01
6 a 2008-8-8 16:01
7 b 2008-8-8 17:01

结果 (同一用户同一天只算一次)
2008-8-8 x
2008-8-9 y
2008-8-10 z

[wer123q]

 select 访问量=count(distinct userid) from tb group by addtime 

[水族杰纶]

declare @t table(id int , userid varchar(3), addtime datetime) 
insert @t select 1, 'a' ,'2008-8-8 11:01' 
insert @t select 2, 'b' ,'2008-8-8 12:01' 
insert @t select 3, 'c' ,'2008-9-8 13:01' 
insert @t select 4, 'a' ,'2008-10-8 14:01' 
insert @t select 5, 'a' ,'2008-8-8 15:01' 
insert @t select 6, 'a' ,'2008-11-8 16:01' 
insert @t select 7, 'b' ,'2008-11-8 17:01' 
select convert(varchar(10),addtime,120),count(distinct userid) from @t group by  convert(varchar(10),addtime,120)
/*---------- ----------- 
2008-08-08 2
2008-09-08 1
2008-10-08 1
2008-11-08 2

(影響 4 個資料列)*/

[lonlyhawk]

--生成测试数据   
select 1 [id],'a' userid,'2008-8-8 11:01' addtime into #t
union all select 2,'b','2008-8-8 12:01' 
union all select 3,'c','2008-8-8 13:01' 
union all select 4,'a','2008-8-8 14:01' 
union all select 5,'a','2008-8-9 15:01' 
union all select 6,'a','2008-8-8 16:01' 
union all select 7,'b','2008-8-10 17:01' 

--查询
select convert(varchar(10),cast(addtime as datetime),20),count(distinct userid) from #t
	group by convert(varchar(10),cast(addtime as datetime),20) 
	order by convert(varchar(10),cast(addtime as datetime),20)

/*结果
2008-08-08	3
2008-08-09	1
2008-08-10	1
*/