数据库的IISLog中记录有客户的访问日志。LogTime是每一次访问的时间,数据格式如 2006-11-15 1:17:23,现在我想得到一个这样的结果:知道一天中以10分钟为一个时段,每个时段有多少人访问网站。
例如最后结果是:
0:00 10人次
0:10 15人次
........
8:30 50次
........
23:50 20人次请教大家,这SQL语句如何写?
谢谢!!
例如最后结果是:
0:00 10人次
0:10 15人次
........
8:30 50次
........
23:50 20人次请教大家,这SQL语句如何写?
谢谢!!
用函数datediff(mi,date1,date2)可以求出date1与date2之间相差的分钟数
(
LogTime datetime
)insert IISLog(LogTime) VALUES('2006-11-21 18:30:23')
insert IISLog(LogTime) VALUES('2006-11-21 18:32:23')
insert IISLog(LogTime) VALUES('2006-11-21 18:33:23')
insert IISLog(LogTime) VALUES('2006-11-21 18:50:23')declare @day datetime
set @day = '2006-11-21 00:00:00'select logTimeT as '时间', sum(1) as '次数'
from (select DATEADD(minute, DATEDIFF(minute, 0, LogTime)/10 * 10,0) logTimeT
from IISLog
where LogTime >= @day and LogTime < dateadd(day, 1, @day)) as IISLog
GROUP BY logTimeT
select cast(DATEPART(Hour, logTimeT) as varchar(2)) + ':' +
cast(DATEPART(minute,logTimeT) as varchar(2)) as '时间',
sum(1) as '次数'
from (select DATEADD(minute, DATEDIFF(minute, DATEADD(day, 0, DATEDIFF(day, 0,LogTime)), LogTime)/10 * 10,0) logTimeT
from IISLog ) as IISLog
GROUP BY logTimeT
convert(varchar(15),时间,120)--取字段做为组就行了
select convert(varchar(15),时间,120),count(*) as 记录
from tablename
group by convert(varchar(15),时间,120)
SET @SERCHDATE = '2006/11/22'SELECT RIGHT(CONVERT(VARCHAR, DATEADD(MINUTE, T.TD * 10, @SERCHDATE), 120), 8), T.CT
FROM (SELECT DATEDIFF(MINUTE, @SERCHDATE, LogTIME) / 10 AS TD, COUNT(1) AS CT
FROM IISLog GROUP BY DATEDIFF(MINUTE, @SERCHDATE, LogTIME) / 10)T
set LogTimeTJ = dateadd(mi , datediff(mi, convert(varchar(10),LogTime,120) + ' 00:00.000' ,LogTime)/10*10, cast(convert(varchar(10),LogTime,120) + ' 00:00.000' as datetime)) select LogTimeTJ , count(*) as 数量 from tb group by LogTimeTJ
from tb
group by dateadd(mi , datediff(mi, convert(varchar(10),LogTime,120) + ' 00:00.000' ,LogTime)/10*10, cast(convert(varchar(10),LogTime,120) + ' 00:00.000' as datetime))