统计某个人的在线时间总和

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    张三
*/