if object_id('tb') is not null drop table tb
go
create table tb([VisitDate] datetime,[State] int,[Vistor] varchar(10))
insert tb select '2008-05-12 00:00:00',0,'张三'
union all select '2008-05-12 02:10:00',1,'张三'
union all select '2008-05-12 02:11:00',0,'张三'
union all select '2008-05-12 02:13:00',1,'张三'
union all select '2008-05-12 03:15:00',1,'张三'
union all select '2008-05-12 03:17:00',1,'张三'
union all select '2008-05-12 03:14:00',1,'李四'
union all select '2008-05-12 03:15:00',1,'李四'
union all select '2008-05-12 04:15:00',1,'李四'
union all select '2008-05-12 03:18:00',1,'张三'
union all select '2008-05-12 03:20:00',1,'张三'
union all select '2008-05-12 03:21:00',0,'张三'
union all select '2008-05-12 04:19:00',1,'李四'
go
统计某个人的在线时间总和:
规则如下:0表示不在线,1表示在线,连续是1的才求差值,否则不求差值,最后把差值求总和算出每个人的在线时间。正确的结果如下:
OnlineTime Vistor
1:07 张三
1:05 李四
select convert(varchar,datediff(mi,min(visitdate),max(visitdate))/60)+':'+right('00'+convert(varchar,datediff(mi,min(visitdate),max(visitdate)) % 60),2) as OnlineTime,vistor from(
select * from tb a where [state]=1
and (exists(select 1 from tb b where [state]=1 and vistor=a.vistor and visitdate>a.visitdate
and not exists(select 1 from tb where [state]=0 and vistor=b.vistor and visitdate<b.visitdate and visitdate>a.visitdate))
or exists(select 1 from tb c where [state]=1 and vistor=a.vistor and visitdate<a.visitdate
and not exists(select 1 from tb where [state]=0 and vistor=c.vistor and visitdate>c.visitdate and visitdate<a.visitdate))
))t group by vistor
go
drop table tb
/*
OnlineTime vistor
----------------------------------- ----------
1:05 李四
1:07 张三
*/
select convert(varchar(10),dateadd(mi,sum(span),'1900-1-1'),114),vistor from
(
select DATEDIFF(mi,min(visitDate),gdate) span,vistor
from
(
select *,(select top 1 visitDate from tb b where vistor = a.vistor and state=1 and visitdate>a.visitdate
and not exists(select 1 from tb where vistor = a.vistor and state=0 and visitdate>a.visitdate and visitdate<b.visitdate)
order by visitdate desc
) gdate from tb a where state=1
) x
group by gdate,vistor
) x group by vistor
/*
01:05:00:0 李四
01:07:00:0 张三
*/