--时间差以S计算,小时H,年Y select user,DateDiff ('s',(select max(dtdate) from tb),getdate()) from tb where user='用户名'
create table tb ( userid varchar(10), loginDate datetime ) insert into tb select '001','2012-04-12' union all select '001','2012-04-13' union all select '001','2012-04-14' union all select '001','2012-04-18' union all select '002','2012-04-12' union all select '002','2012-04-13'select *,datediff (DAY,a.loginDate ,(select top 1 b.loginDate from tb b where a.userid =b.userid and a.loginDate <b.loginDate )) from tb a
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 */
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 */
TravyLee 大神 我的 MS SQL 是2008,运行您的代码,说“列名 'row_num' 无效。”,请问这个问题该怎么解决
--时间差以S计算,小时H,年Y
select user,DateDiff ('s',(select max(dtdate) from tb),getdate()) from tb where user='用户名'
create table tb
(
userid varchar(10),
loginDate datetime
)
insert into tb
select '001','2012-04-12' union all
select '001','2012-04-13' union all
select '001','2012-04-14' union all
select '001','2012-04-18' union all
select '002','2012-04-12' union all
select '002','2012-04-13'select *,datediff (DAY,a.loginDate ,(select top 1 b.loginDate from tb b where a.userid =b.userid and a.loginDate <b.loginDate )) from tb a
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
*/
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
*/
我的 MS SQL 是2008,运行您的代码,说“列名 'row_num' 无效。”,请问这个问题该怎么解决