select username,count(1) as 次数
from T
where login between @t1 and @t2 or logout between @t1 and @t2
group by username
from T
where login between @t1 and @t2 or logout between @t1 and @t2
group by username
调试欢乐多
from
(
select username,dif=datediff(minute,login,logout)
from tb
) a
order by dif desc
select ...定义时间段
select * from T where login between @startdate and @enddate--在线人数时间段按小时还是按?
from
(
select username,dif=datediff(minute,login,logout)
from tb
) a
where login between '2008-01-01' and '2008-02-01'
and logout between '2008-01-01' and '2008-02-01'
order by dif desc
declare @startdate datetime,@enddate datetime
select @startdate='',@enddate=''
select
t2.时间段,count(1)
from
@T t2
left join
t on ...
where
login between @startdate and @enddate
or Logout between @startdate and @enddate
declare @dt1 as datetime
declare @dt2 as datetime
set @dt1 = '2008-01-01 00:00:00'
set @dt2 = '2008-01-01 20:00:00'select top 1 username , 时间 = sum(datediff(ss,login,logout)) from tb where Login<=@dt1 and logout >= @dt2 group by username order by 时间 desc
drop table tb
go
create table tb(UserName varchar(10),Login datetime,Logout datetime)
insert into tb
select 'guest1','2008-1-1 10:12:1','2008-1-1 13:12:1' union all
select 'guest2','2008-1-1 11:12:1','2008-1-1 13:12:1' union all
select 'guest3','2008-1-1 12:12:1','2008-1-1 15:12:1' union all
select 'guest4','2008-1-1 11:12:1','2008-1-1 19:12:1' union all
select 'guest1','2008-1-1 10:15:1','2008-1-1 16:12:1'
select * from tbdeclare @begin datetime,@end datetime
select @begin='2008-1-1 10:10:1',@end='2008-1-1 20:00:0'select top 1 username from
(
select UserName,sum(datediff(ss,Login,Logout)) as '在线时长' from tb
where Logout<@end
group by UserName
) a
order by 在线时长接分了
from T
where login between @t1 and @t2 or logout between @t1 and @t2
group by convert(char(16),@login,120)
这是以小时为单位(登录时间)的统计在线人数
insert @t1(UserName,Login,Logout) Values('guest1','2008-1-1 10:12:1','2008-1-1 13:12:1')
insert @t1(UserName,Login,Logout) Values('guest2','2008-1-1 11:12:1','2008-1-1 13:12:1')
insert @t1(UserName,Login,Logout) Values('guest3','2008-1-1 12:12:1','2008-1-1 15:12:1')
insert @t1(UserName,Login,Logout) Values('guest4','2008-1-1 11:12:1','2008-1-1 19:12:1')
insert @t1(UserName,Login,Logout) Values('guest5','2008-1-1 10:15:1','2008-1-1 16:12:1')declare @t2 table(ID int identity(1,1),Login Datetime,Logout Datetime,UserCount int)
insert @t2(Login) select distinct T from (select Login as T from @t1 union select Logout as T from @t1) as tb order by TCreate table #t3 (Login Datetime,Logout Datetime,UserCount int)
insert #t3(Login,Logout,UserCount)
select a.Login,b.Login,0 from @t2 as a,@t2 as b where a.ID=b.ID-1update #t3 set UserCount=d.UserCount
from
(select #t3.Login,#t3.Logout,Count(*) as UserCount from @t1 as c,#t3 where c.Login<=#t3.Login and #t3.Logout<=c.Logout group by #t3.Login,#t3.Logout) as d
where #t3.Login=d.Login and #t3.Logout=d.Logoutselect * from #t3drop table #t3
结果:
Login Logout UserCount
-------------------------------------
2008-01-01 10:12:01.000 2008-01-01 10:15:01.000 1
2008-01-01 10:15:01.000 2008-01-01 11:12:01.000 2
2008-01-01 11:12:01.000 2008-01-01 12:12:01.000 4
2008-01-01 12:12:01.000 2008-01-01 13:12:01.000 5
2008-01-01 13:12:01.000 2008-01-01 15:12:01.000 3
2008-01-01 15:12:01.000 2008-01-01 16:12:01.000 2
2008-01-01 16:12:01.000 2008-01-01 19:12:01.000 1
你的结果大致符合要求
实际上我要的结果是
select max(UserCount) from #t3 但是我的数据量很大,我不知道这种方法的效率如何
数据量可能有几万,也可能有几十几百万
declare @dt1 as datetime
declare @dt2 as datetime
set @dt1 = '2008-01-01 00:00:00'
set @dt2 = '2008-01-01 20:00:00'
select count(*) from tb
where login between convert(datetiem,@dt1) and convert(datetime,@dt2)
or logout between convert(datetiem,@dt1) and convert(datetime,@dt2)
declare @t1 table(UserName varchar(10),Login Datetime,Logout Datetime)
insert @t1(UserName,Login,Logout) Values('guest1','2008-1-1 10:12:1','2008-1-1 13:12:1')
insert @t1(UserName,Login,Logout) Values('guest2','2008-1-1 11:12:1','2008-1-1 13:12:1')
insert @t1(UserName,Login,Logout) Values('guest3','2008-1-1 12:12:1','2008-1-1 15:12:1')
insert @t1(UserName,Login,Logout) Values('guest4','2008-1-1 11:12:1','2008-1-1 19:12:1')
insert @t1(UserName,Login,Logout) Values('guest5','2008-1-1 10:15:1','2008-1-1 16:12:1')
declare @t2 table(hh int,dt datetime)
declare @dt1 datetime,@dt2 datetime
select @dt1 = '2008-01-01',@dt2 = '2008-01-01 16:12:0'
insert @t2
select (select count(*) from @T1 where a.dt between login and logout) as hh,a.dt as dt
from (
select login as dt from @T1 where login >= @dt1
union select logout as dt from @t1 where logout<=@dt2
) a select '最大在线人数为:'+rtrim(hh)+'人,出现在:'+convert(varchar(20),dt,120)
from @t2 where hh = (select max(hh) from @t2)
/*
-----------------------------------------------------------
最大在线人数为:5人,出现在:2008-01-01 12:12:01
最大在线人数为:5人,出现在:2008-01-01 13:12:01
*/
本来没有记录,最大在线人数为0,可是这么还是5啊
lz 试试。declare @t1 table(UserName varchar(10),Login Datetime,Logout Datetime)
insert @t1(UserName,Login,Logout) Values('guest1','2008-1-1 10:12:1','2008-1-1 13:12:1')
insert @t1(UserName,Login,Logout) Values('guest2','2008-1-1 11:12:1','2008-1-1 13:12:1')
insert @t1(UserName,Login,Logout) Values('guest3','2008-1-1 12:12:1','2008-1-1 15:12:1')
insert @t1(UserName,Login,Logout) Values('guest4','2008-1-1 11:12:1','2008-1-1 19:12:1')
insert @t1(UserName,Login,Logout) Values('guest5','2008-1-1 10:15:1','2008-1-1 16:12:1') declare @t2 table(hh int,dt datetime)
declare @dt1 datetime,@dt2 datetime
--select @dt1 = '2008-01-02',@dt2 = '2008-01-02 16:12:0'
select @dt1 = '2008-01-01',@dt2 = '2008-01-01 16:12:0'
insert @t2
select (select count(*) from @T1 where a.login between login and logout) as hh,a.login as dt
from @T1 a where login >= @dt1
select '最大在线人数为:'+rtrim(hh)+'人,出现在:'+convert(varchar(20),dt,120)
from @t2 where hh = (select max(hh) from @t2)
declare @t1 table(UserName varchar(10),Login Datetime,Logout Datetime)
insert @t1(UserName,Login,Logout) Values('guest1','2008-1-1 10:12:1','2008-1-1 13:12:1')
insert @t1(UserName,Login,Logout) Values('guest2','2008-1-1 11:12:1','2008-1-1 13:12:1')
insert @t1(UserName,Login,Logout) Values('guest3','2008-1-1 12:12:1','2008-1-1 15:12:1')
insert @t1(UserName,Login,Logout) Values('guest4','2008-1-1 11:12:1','2008-1-1 19:12:1')
insert @t1(UserName,Login,Logout) Values('guest5','2008-1-1 10:15:1','2008-1-1 16:12:1')
declare @t2 table(hh int,dt datetime)
declare @dt1 datetime,@dt2 datetime
--select @dt1 = '2008-01-02',@dt2 = '2008-01-02 16:12:0'
select @dt1 = '2008-01-01',@dt2 = '2008-01-01 16:12:0'
insert @t2
select (select count(*) from @T1 where a.login between login and logout) as hh,a.login as dt
from @T1 a where login between @dt1 and @dt2select '最大在线人数为:'+rtrim(hh)+'人,出现在:'+convert(varchar(20),dt,120)
from @t2 where hh = (select max(hh) from @t2)
/*
-----------------------------------------------------------
最大在线人数为:5人,出现在:2008-01-01 12:12:01(所影响的行数为 1 行)
*/
select '最大在线人数为:'+rtrim(hh)+'人,出现在:'+convert(varchar(20),dt,120)
from (select (select count(*) from @T1 where a.login between login and logout) as hh,a.login as dt
from @T1 a where login between @dt1 and @dt2) a
where hh = (select max(hh)
from (select (select count(*) from @T1 where a.login between login and logout) as hh,a.login as dt
from @T1 a where login between @dt1 and @dt2) b)
from T
where login between @t1 and @t2 or logout between @t1 and @t2
group by username另外表示时间的字段上面加索引