如何取得系统登录时间？

我现在的数据是这样的，userid表示用户登录系统的代码，time表示登录登出系统的时间，status，1表示登录，2表示登出；
登出的记录有可能会丢失，例如，突然断网断点之类的，登出的记录可能没有办法取到。
userid time status
user001 2011-06-10 10:00:00.000 1
user001 2011-06-10 14:57:24.000 1
user001 2011-06-10 15:03:41.000 2
user001 2011-06-10 16:50:00.000 1
user001 2011-06-10 17:03:40.000 2
user001 2011-06-10 17:20:40.000 1

user002 2011-06-10 10:01:20.000 1
user002 2011-06-10 10:20:24.000 1
user002 2011-06-10 10:40:40.000 2
user002 2011-06-10 13:50:00.000 1
user002 2011-06-10 15:03:40.000 2  
我想要的结果是：
用户代码 登录时间区间  
user001 2011-06-10 10:00:00.000-- null 0 minutes  
user001 2011-06-10 14:57:24.000--2011-06-10 15:03:41.000 6 minutes  
user001 2011-06-10 16:50:00.000--2011-06-10 17:03:40.000 13 minutes
user001 2011-06-10 17:20:40.000 null 0 minutes
user002 2011-06-10 10:01:20.000--null 0 minutes  
user002 2011-06-10 10:20:24.000-- 2011-06-10 10:40:40.000 20 minutes
user002 2011-06-10 13:50:00.000--2011-06-10 15:03:40.000 73minutes  

我的目的是统计每个用户登录系统的时间的汇总。 
 
 

SQL code

create table tb(userid varchar(10),time datetime,status int)
insert into tb select 'user001','2011-06-10 10:00:00.000',1
insert into tb select 'user001','2011-06-10 14:57:24.000',1
insert into tb select 'user001','2011-06-10 15:03:41.000',2
insert into tb select 'user001','2011-06-10 16:50:00.000',1
insert into tb select 'user001','2011-06-10 17:03:40.000',2
insert into tb select 'user001','2011-06-10 17:20:40.000',1
insert into tb select 'user002','2011-06-10 10:01:20.000',1
insert into tb select 'user002','2011-06-10 10:20:24.000',1
insert into tb select 'user002','2011-06-10 10:40:40.000',2
insert into tb select 'user002','2011-06-10 13:50:00.000',1
insert into tb select 'user002','2011-06-10 15:03:40.000',2 
go
;with cte as(
select row_number()over(order by userid,time)rn,* from tb
)select a.userid,a.time,b.time,datediff(mi,a.time,b.time) 
from cte a left join cte b on a.userid=b.userid and a.status=1 and b.status=2 and a.rn=b.rn-1
/*
userid     time                    time                    
---------- ----------------------- ----------------------- -----------
user001    2011-06-10 10:00:00.000 NULL                    NULL
user001    2011-06-10 14:57:24.000 2011-06-10 15:03:41.000 6
user001    2011-06-10 15:03:41.000 NULL                    NULL
user001    2011-06-10 16:50:00.000 2011-06-10 17:03:40.000 13
user001    2011-06-10 17:03:40.000 NULL                    NULL
user001    2011-06-10 17:20:40.000 NULL                    NULL
user002    2011-06-10 10:01:20.000 NULL                    NULL
user002    2011-06-10 10:20:24.000 2011-06-10 10:40:40.000 20
user002    2011-06-10 10:40:40.000 NULL                    NULL
user002    2011-06-10 13:50:00.000 2011-06-10 15:03:40.000 73
user002    2011-06-10 15:03:40.000 NULL                    NULL

(11 行受影响)

*/
go
drop table tb

SQL code

create table tb(userid varchar(10),time datetime,status int)
insert into tb select 'user001','2011-06-10 10:00:00.000',1
insert into tb select 'user001','2011-06-10 14:57:24.000',1
insert into tb select 'user001','2011-06-10 15:03:41.000',2
insert into tb select 'user001','2011-06-10 16:50:00.000',1
insert into tb select 'user001','2011-06-10 17:03:40.000',2
insert into tb select 'user001','2011-06-10 17:20:40.000',1
insert into tb select 'user002','2011-06-10 10:01:20.000',1
insert into tb select 'user002','2011-06-10 10:20:24.000',1
insert into tb select 'user002','2011-06-10 10:40:40.000',2
insert into tb select 'user002','2011-06-10 13:50:00.000',1
insert into tb select 'user002','2011-06-10 15:03:40.000',2 
go
;with cte as(
select row_number()over(order by userid,time)rn,* from tb
)select a.userid,a.time,b.time,isnull(datediff(mi,a.time,b.time),0) as n,'minuters'as unit
from cte a left join cte b on a.userid=b.userid and a.status=1 and b.status=2 and a.rn=b.rn-1
/*
userid     time                    time                    n           unit
---------- ----------------------- ----------------------- ----------- --------
user001    2011-06-10 10:00:00.000 NULL                    0           minuters
user001    2011-06-10 14:57:24.000 2011-06-10 15:03:41.000 6           minuters
user001    2011-06-10 15:03:41.000 NULL                    0           minuters
user001    2011-06-10 16:50:00.000 2011-06-10 17:03:40.000 13          minuters
user001    2011-06-10 17:03:40.000 NULL                    0           minuters
user001    2011-06-10 17:20:40.000 NULL                    0           minuters
user002    2011-06-10 10:01:20.000 NULL                    0           minuters
user002    2011-06-10 10:20:24.000 2011-06-10 10:40:40.000 20          minuters
user002    2011-06-10 10:40:40.000 NULL                    0           minuters
user002    2011-06-10 13:50:00.000 2011-06-10 15:03:40.000 73          minuters
user002    2011-06-10 15:03:40.000 NULL                    0           minuters

(11 行受影响)

*/
go
drop table tb

SQL code

create table tb(userid varchar(10),time datetime,status int)
insert into tb select 'user001','2011-06-10 10:00:00.000',1
insert into tb select 'user001','2011-06-10 14:57:24.000',1
insert into tb select 'user001','2011-06-10 15:03:41.000',2
insert into tb select 'user001','2011-06-10 16:50:00.000',1
insert into tb select 'user001','2011-06-10 17:03:40.000',2
insert into tb select 'user001','2011-06-10 17:20:40.000',1
insert into tb select 'user002','2011-06-10 10:01:20.000',1
insert into tb select 'user002','2011-06-10 10:20:24.000',1
insert into tb select 'user002','2011-06-10 10:40:40.000',2
insert into tb select 'user002','2011-06-10 13:50:00.000',1
insert into tb select 'user002','2011-06-10 15:03:40.000',2 
go
;with cte as(
select row_number()over(order by userid,time)rn,* from tb
)select a.userid,a.time,b.time,isnull(datediff(mi,a.time,b.time),0) as n,'minuters'as unit
from cte a left join cte b on a.userid=b.userid and a.rn=b.rn-1 and b.status=2
where a.status=1
/*
userid     time                    time                    n           unit
---------- ----------------------- ----------------------- ----------- --------
user001    2011-06-10 10:00:00.000 NULL                    0           minuters
user001    2011-06-10 14:57:24.000 2011-06-10 15:03:41.000 6           minuters
user001    2011-06-10 16:50:00.000 2011-06-10 17:03:40.000 13          minuters
user001    2011-06-10 17:20:40.000 NULL                    0           minuters
user002    2011-06-10 10:01:20.000 NULL                    0           minuters
user002    2011-06-10 10:20:24.000 2011-06-10 10:40:40.000 20          minuters
user002    2011-06-10 13:50:00.000 2011-06-10 15:03:40.000 73          minuters

(7 行受影响)

*/
go
drop table tb

SQL code

WITH BTS AS
(
    SELECT *,ROW_NUMBER() OVER (PARTITION BY userid ORDER BY time) RM FROM 表名
),
BS AS
(
SELECT *,(SELECT time FROM BTS WHERE userid=A.userid 
             AND status=2 AND RM=A.RM+1) OUTTime
  FROM BTS A
 WHERE status=1
)
SELECT *,ISNULL(DATEDIFF(Mi,time,OUTTime),0) FROM BS

mark 不错

+