select max(c) from ( select count(*) as c from a , a b where a.start_time between b.start_time and b.end_time and a.start_time between @起始查询时间 and @截止查询时间 group by a.start_time ) c
可以查到每条记录时的在线人数: select id,start_time,end_time, (select count(*) from A表 where start_time>=t.start_time and start_time<=t.end_time or end_time>=t.start_time and end_time<=t.end_time) 在线人数 from A表 t
--查询declare @start_time datetime,@end_time datetime,@i int select @start_time=min(start_time) --统计的开始时间,可以直接指定值 ,@end_time=max(end_time) --统计的结束时间,可以直接指定值 from A表--取得最大可分拆的记录条数 select @i=max(ceiling(datediff(minute,@start_time,end_time)/10.0) -datediff(minute,@start_time,start_time)/10) from A表 where end_time>@start_time and start_time<@end_time--有满足条件的数据才处理 if @i>0 begin --创建处理的临时表 set rowcount @i select id=identity(int,0,1),a=0 into #t from syscolumns set @i=@i-@@rowcount while @i>0 begin set rowcount @i insert #t(a) select 0 from syscolumns set @i=@i-@@rowcount end set rowcount 0 --统计 select 开始时间=dateadd(minute,dt*10,@start_time) ,结束时间=dateadd(minute,dt*10+9,@start_time) ,在线人数=人数 from( select dt=datediff(minute,@start_time,start_time)/10+b.id,人数=count(*) from A表 a,#t b where b.id<(ceiling(datediff(minute,@start_time,end_time)/10.0) -datediff(minute,@start_time,start_time)/10) group by datediff(minute,@start_time,start_time)/10+b.id )a order by 人数 desc,dt
--删除处理的临时表 drop table #t end
--测试--测试数据 create table A表(id int identity(1,1),start_time datetime,end_time datetime) insert A表 select '2004-02-01 08:00:00','2004-02-01 08:21:00' union all select '2004-02-01 08:15:00','2004-02-01 09:09:00' union all select '2004-02-01 08:15:00','2004-02-01 08:19:00' go--查询 declare @start_time datetime,@end_time datetime,@i int select @start_time=min(start_time) --统计的开始时间,可以直接指定值 ,@end_time=max(end_time) --统计的结束时间,可以直接指定值 from A表--取得最大可分拆的记录条数 select @i=max(ceiling(datediff(minute,@start_time,end_time)/10.0) -datediff(minute,@start_time,start_time)/10) from A表 where end_time>@start_time and start_time<@end_time--有满足条件的数据才处理 if @i>0 begin --创建处理的临时表 set rowcount @i select id=identity(int,0,1),a=0 into #t from syscolumns set @i=@i-@@rowcount while @i>0 begin set rowcount @i insert #t(a) select 0 from syscolumns set @i=@i-@@rowcount end set rowcount 0 --统计 select 开始时间=dateadd(minute,dt*10,@start_time) ,结束时间=dateadd(minute,dt*10+9,@start_time) ,在线人数=人数 from( select dt=datediff(minute,@start_time,start_time)/10+b.id,人数=count(*) from A表 a,#t b where b.id<(ceiling(datediff(minute,@start_time,end_time)/10.0) -datediff(minute,@start_time,start_time)/10) group by datediff(minute,@start_time,start_time)/10+b.id )a order by 人数 desc,dt
确切的说 pbsql(风云) 的是与某人同时在线的人数,也算合理:select id,start_time,end_time ,'与' + ID + '同时在线的人数共有' + (select count(*) from A表 where start_time>=t.start_time and start_time<=t.end_time or end_time>=t.start_time and end_time<=t.end_time) 与 from A表 t
select max(c)
from
(
select count(*) as c
from a , a b
where a.start_time between b.start_time and b.end_time
and a.start_time between @起始查询时间 and @截止查询时间
group by a.start_time
) c
select id,start_time,end_time,
(select count(*) from A表
where start_time>=t.start_time and start_time<=t.end_time
or end_time>=t.start_time and end_time<=t.end_time) 在线人数
from A表 t
select @start_time=min(start_time) --统计的开始时间,可以直接指定值
,@end_time=max(end_time) --统计的结束时间,可以直接指定值
from A表--取得最大可分拆的记录条数
select @i=max(ceiling(datediff(minute,@start_time,end_time)/10.0)
-datediff(minute,@start_time,start_time)/10)
from A表
where end_time>@start_time and start_time<@end_time--有满足条件的数据才处理
if @i>0
begin --创建处理的临时表
set rowcount @i
select id=identity(int,0,1),a=0 into #t from syscolumns
set @i=@i-@@rowcount
while @i>0
begin
set rowcount @i
insert #t(a) select 0 from syscolumns
set @i=@i-@@rowcount
end
set rowcount 0 --统计
select 开始时间=dateadd(minute,dt*10,@start_time)
,结束时间=dateadd(minute,dt*10+9,@start_time)
,在线人数=人数
from(
select dt=datediff(minute,@start_time,start_time)/10+b.id,人数=count(*)
from A表 a,#t b
where b.id<(ceiling(datediff(minute,@start_time,end_time)/10.0)
-datediff(minute,@start_time,start_time)/10)
group by datediff(minute,@start_time,start_time)/10+b.id
)a order by 人数 desc,dt
--删除处理的临时表
drop table #t
end
create table A表(id int identity(1,1),start_time datetime,end_time datetime)
insert A表 select '2004-02-01 08:00:00','2004-02-01 08:21:00'
union all select '2004-02-01 08:15:00','2004-02-01 09:09:00'
union all select '2004-02-01 08:15:00','2004-02-01 08:19:00'
go--查询
declare @start_time datetime,@end_time datetime,@i int
select @start_time=min(start_time) --统计的开始时间,可以直接指定值
,@end_time=max(end_time) --统计的结束时间,可以直接指定值
from A表--取得最大可分拆的记录条数
select @i=max(ceiling(datediff(minute,@start_time,end_time)/10.0)
-datediff(minute,@start_time,start_time)/10)
from A表
where end_time>@start_time and start_time<@end_time--有满足条件的数据才处理
if @i>0
begin --创建处理的临时表
set rowcount @i
select id=identity(int,0,1),a=0 into #t from syscolumns
set @i=@i-@@rowcount
while @i>0
begin
set rowcount @i
insert #t(a) select 0 from syscolumns
set @i=@i-@@rowcount
end
set rowcount 0 --统计
select 开始时间=dateadd(minute,dt*10,@start_time)
,结束时间=dateadd(minute,dt*10+9,@start_time)
,在线人数=人数
from(
select dt=datediff(minute,@start_time,start_time)/10+b.id,人数=count(*)
from A表 a,#t b
where b.id<(ceiling(datediff(minute,@start_time,end_time)/10.0)
-datediff(minute,@start_time,start_time)/10)
group by datediff(minute,@start_time,start_time)/10+b.id
)a order by 人数 desc,dt
--删除处理的临时表
drop table #t
end
go--删除测试
drop table A表/*--测试结果开始时间 结束时间 在线人数
-------------------------- -------------------------- -----------
2004-02-01 08:10:00.000 2004-02-01 08:19:00.000 3
2004-02-01 08:20:00.000 2004-02-01 08:29:00.000 2
2004-02-01 08:00:00.000 2004-02-01 08:09:00.000 1
2004-02-01 08:30:00.000 2004-02-01 08:39:00.000 1
2004-02-01 08:40:00.000 2004-02-01 08:49:00.000 1
2004-02-01 08:50:00.000 2004-02-01 08:59:00.000 1
2004-02-01 09:00:00.000 2004-02-01 09:09:00.000 1(所影响的行数为 7 行)
--*/
,'与' + ID + '同时在线的人数共有' + (select count(*) from A表
where start_time>=t.start_time and start_time<=t.end_time
or end_time>=t.start_time and end_time<=t.end_time) 与
from A表 t