求用户登录的时间差
一个记录表，记录了用户的每次登录时间，如何求单个用户，每次登录与前一次登录的时间差。表里没有主键。
[解决办法]
goif object_id('tbl')is not nulldrop table tblgocreate table tbl(userid varchar(5),logindate datetime)goinsert tblselect '1001','2012-04-16 08:23:16' union allselect '1001','2012-04-16 12:34:00' union allselect '1001','2012-04-16 14:17:16' union allselect '1001','2012-04-17 08:23:16' union allselect '1002','2012-04-15 08:23:16' union allselect '1002','2012-04-16 12:34:00' union allselect '1002','2012-04-16 14:17:16' union allselect '1002','2012-04-16 23:56:12';with tas(select *,row_num=ROW_NUMBER()over(partition by userid order by logindate asc)from tbl)select a.userid,a.logindate,datediff(HH,isnull(b.logindate,a.logindate),a.logindate) as [hours]from t aleft join t b on a.userid=b.userid and a.row_num=b.row_num+1where a.userid='1001'/*userid logindate hours1001 2012-04-16 08:23:16.000 01001 2012-04-16 12:34:00.000 41001 2012-04-16 14:17:16.000 21001 2012-04-17 08:23:16.000 18*/
[解决办法]
goif object_id('tbl')is not nulldrop table tblgocreate table tbl(userid varchar(5),logindate datetime)goinsert tblselect '1001','2012-04-16 08:23:16' union allselect '1001','2012-04-16 12:34:00' union allselect '1001','2012-04-16 14:17:16' union allselect '1001','2012-04-17 08:23:16' union allselect '1002','2012-04-15 08:23:16' union allselect '1002','2012-04-16 12:34:00' union allselect '1002','2012-04-16 14:17:16' union allselect '1002','2012-04-16 23:56:12'--2005;with tas(select *,row_num=ROW_NUMBER()over(partition by userid order by logindate asc)from tbl)select a.userid,a.logindate,datediff(HH,isnull(b.logindate,a.logindate),a.logindate) as [hours]from t aleft join t b on a.userid=b.userid and a.row_num=b.row_num+1where a.userid='1001'--2000select *,isnull(DATEDIFF(HH,(select top 1 b.logindate from tbl b where b.logindate<a.logindate and b.userid=a.userid),a.logindate),0) as [hours]from tbl a where userid='1001'/*userid logindate hours1001 2012-04-16 08:23:16.000 01001 2012-04-16 12:34:00.000 41001 2012-04-16 14:17:16.000 21001 2012-04-17 08:23:16.000 18*/