帮忙写一个SQL语句(SQL2005存储过程中),详情如下:
userID logintime logoutTime
01 22 23
01 05 null
01 11 13
02 15 20
01 07 null
用户1在5点登陆,结果退出时间没有记录到,下一次的登陆时间是7点,所以就把5点登陆那次的退出时间改成7点
以此类推要把7点登陆那次的退出时间改为下一次的登陆时间,也就是11点
求实现此更新功能的SQL语句!
userID logintime logoutTime
01 22 23
01 05 null
01 11 13
02 15 20
01 07 null
用户1在5点登陆,结果退出时间没有记录到,下一次的登陆时间是7点,所以就把5点登陆那次的退出时间改成7点
以此类推要把7点登陆那次的退出时间改为下一次的登陆时间,也就是11点
求实现此更新功能的SQL语句!
update a
set a.logoutTime = (select top 1 logintime from tb
where userID = a.userID and logintime > a.logintime order by logintime)
from tb a
where logoutTime is null
create table tb(userID varchar(10),logintime datetime,logoutTime datetime)
insert into tb
select '01' ,'2011-08-31 22:00' ,'2011-08-31 23:00' union all
select '01' ,'2011-08-31 05:00' ,null union all
select '01' ,'2011-08-31 11:00' ,'2011-08-31 13:00' union all
select '02' ,'2011-08-31 15:00' ,'2011-08-31 20:00' union all
select '01' ,'2011-08-31 07:00' ,null
goupdate a
set a.logoutTime = (select top 1 logintime from tb
where userID = a.userID and logintime > a.logintime order by logintime)
from tb a
where logoutTime is nullselect *
from tbdrop table tb/*****************userID logintime logoutTime
---------- ----------------------- -----------------------
01 2011-08-31 22:00:00.000 2011-08-31 23:00:00.000
01 2011-08-31 05:00:00.000 2011-08-31 07:00:00.000 --
01 2011-08-31 11:00:00.000 2011-08-31 13:00:00.000
02 2011-08-31 15:00:00.000 2011-08-31 20:00:00.000
01 2011-08-31 07:00:00.000 2011-08-31 11:00:00.000 --(5 行受影响)
--也可以这样
update a
set a.logoutTime = (select min(logintime) from tb
where userID = a.userID and logintime > a.logintime )
from tb a
where logoutTime is null