34,575
社区成员
发帖
与我相关
我的任务
分享
create table t(ip varchar(30),[time] varchar(30),[address] varchar(50))
insert into t
select '192.168.1.3','20061028140125','http://localhost/' union all
select '192.168.1.3','20061028140125','http://localhost/' union all
select '192.168.1.3','20061028140125','http://localhost/' union all
select '192.168.1.3','20061028140125','http://localhost/' union all
select '192.168.1.3','20061028140125','http://localhost/' union all
select '192.168.1.3','20061120140125','http://localhost/' union all
select '192.168.1.3','20061201140125','http://localhost/' union all
select '192.168.1.3','20061201140125','http://localhost/' union all
select '192.168.1.3','20061201140125','http://localhost/' union all
select '192.168.1.3','20061201140125','http://localhost/'
select
[每年]=(select top 1 count(*) as total from t group by left([time],8) order by total desc),
[每周]=(select top 1 count(*) as total from t group by datepart(week,left([time],8)) order by total desc),
[每月]=(select top 1 count(*) as total from t group by left([time],6) order by total desc),
[当月]=(select count(*) from t where datepart(week,left([time],8))=datepart(week,getdate())),
[当周]=(select count(*) from t where datepart(week,left([time],8))=datepart(week,getdate())-1)