怎么计算在线时间的考勤集合_Sql Server

如何计算在线时间的考勤集合
数据表T用来记录用户登录、注销信息，其中只有4个记录：
user（用户名） operate（操作） time（时间）
LiMing Login 2010/10/24 8:03
WangYi Login 2010/10/24 8:14
WangYi Logout 2010/10/24 16:14
LiMing Logout 2010/10/24 16:44
请写出一个SQL查询语句，返回用户在线时间情况的结果集：
LiMing 8:13
WangYi 8:46

------解决方案--------------------

SQL code

create table tb(  [user] varchar(20),  operate varchar(20),  [time] datetime)insert into tbselect 'LiMing', 'Login', '2010/10/24 8:03' union allselect 'WangYi', 'Login', '2010/10/24 8:14' union allselect 'WangYi', 'Logout', '2010/10/24 16:14' union allselect 'LiMing', 'Logout', '2010/10/24 16:44'select a.[user] , cast(DATEdiff(MI, b.[time] ,a.[time] )/ 60 as varchar) + ':' +  cast(DATEdiff(MI, b.[time] ,a.[time] )%60 as varchar) AS 在线时间  from (select * from tb  where operate ='Logout') a,(select * from tb where operate ='Login') b where a.[user] =b.[user]
------解决方案--------------------
探讨

大虾，难道真的需要这么麻烦吗？

------解决方案--------------------
with tb as (
	select 'LiMing' [User] ,'Login' operate ,convert(datetime,'2010/10/24 8:03') [time] union all
	select 'WangYi' ,'Login' ,convert(datetime,'2010/10/24 8:14') union all
	select 'WangYi' ,'Logout' ,convert(datetime,'2010/10/24 8:22')union all
	select 'LiMing' ,'Logout' ,convert(datetime,'2010/10/24 16:44') union all
	select 'WangYi' ,'Login' ,convert(datetime,'2010/10/25 8:14') union all
	select 'WangYi' ,'Logout' ,convert(datetime,'2010/10/25 16:14')
)
,tb2 as (
select *
,ROW_NUMBER() over(order by [user],[time]) as id
from tb )
select *
,(select CONVERT(nvarchar(50), DATEDIFF(MI,t.[time],[time])/60) +':' +CONVERT(nvarchar(50), DATEDIFF(MI,t.[time],[time])%60) from tb2 where id=t.id+1) [在线时间/秒]
from tb2 t
where t.operate='Login' and exists(select * from tb2 where id=t.id+1 and t.[User]=[User] and t.operate<>operate)