go
if object_id('tbl')is not null
drop table tbl
go
create table tbl(
userid varchar(5),
logindate datetime
)
go
insert tbl
select '1001','2012-04-16 08:23:16' union all
select '1001','2012-04-16 12:34:00' union all
select '1001','2012-04-16 14:17:16' union all
select '1001','2012-04-17 08:23:16' union all
select '1002','2012-04-15 08:23:16' union all
select '1002','2012-04-16 12:34:00' union all
select '1002','2012-04-16 14:17:16' union all
select '1002','2012-04-16 23:56:12'

;with t
as(
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 a
left join t b on a.userid=b.userid and a.row_num=b.row_num+1
where a.userid='1001'
/*
userid    logindate    hours
1001    2012-04-16 08:23:16.000    0
1001    2012-04-16 12:34:00.000    4
1001    2012-04-16 14:17:16.000    2
1001    2012-04-17 08:23:16.000    18
*/

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

SQL code

go
if object_id('tbl')is not null
drop table tbl
go
create table tbl(
userid varchar(5),
logindate datetime
)
go
insert tbl
select '1001','2012-04-16 08:23:16' union all
select '1001','2012-04-16 12:34:00' union all
select '1001','2012-04-16 14:17:16' union all
select '1001','2012-04-17 08:23:16' union all
select '1002','2012-04-15 08:23:16' union all
select '1002','2012-04-16 12:34:00' union all
select '1002','2012-04-16 14:17:16' union all
select '1002','2012-04-16 23:56:12'
--2005
;with t
as(
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 a
left join t b on a.userid=b.userid and a.row_num=b.row_num+1
where a.userid='1001'

--2000
select *,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    hours
1001    2012-04-16 08:23:16.000    0
1001    2012-04-16 12:34:00.000    4
1001    2012-04-16 14:17:16.000    2
1001    2012-04-17 08:23:16.000    18
*/