27,579
社区成员
发帖
与我相关
我的任务
分享
--生成测试数据
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
*/
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 個資料列)*/
select 访问量=count(distinct userid) from tb group by addtime