id 人员帐号 时间 事件(100为登入系统,101为登出系统) 1 a 2005-5-3 11:31:1 100 2 b 2005-5-3 12:34:1 100 3 a 2005-5-3 18:11:2 101 ...由于需要把这些数据存入另外一个表 id 人员帐号 登入时间 登出时间 1 a 2005-5-3 11:31:1 2005-5-3 18:11:2 2 b 2005-5-3 12:34:1 null ...由于原表中的记录的数据有些不正常(登出过程楼记了一部分),所以需要对里面的数据进行判断,比如排序后,如果一个帐号本条记录是登入,下条记录还是登入,那么就把本条记录的登出时间定为下条记录的登出时间。反正需要对数据进行校正。
--看你的需求,不知道是不是這個意思create table T(id int identity(1,1),userid varchar(10),[date] datetime,flag int) insert into T select 'A','2005-5-3 11:31:10',100 insert into T select 'B','2005-5-3 12:34:10',100 insert into T select 'A','2005-5-3 18:11:10',101 insert into T select 'B','2005-5-3 19:11:10',100 insert into T select 'C','2005-5-3 20:31:10',100 insert into T select 'C','2005-5-3 21:11:10',101 insert into T select 'A','2005-5-3 22:11:10',100 insert into T select 'B','2005-5-3 23:11:10',101 insert into T select 'A','2005-5-3 23:21:10',101 GOselect A.id,A.userid, A.[date] as [Login] ,B.[date] as [Logout] from (select id,userid,[date],flag from T where flag=100) A left join (select id,userid,[date],flag from T where flag=101) B on A.userid=B.userid and B.[id]>A.[id] and not exists(select 1 from T where userid=A.userid and id>A.id and id<B.id) /* id userid login logout ---------------------------------------------------------------- 1 A 2005-05-03 11:31:10.000 2005-05-03 18:11:10.000 2 B 2005-05-03 12:34:10.000 NULL 4 B 2005-05-03 19:11:10.000 2005-05-03 23:11:10.000 5 C 2005-05-03 20:31:10.000 2005-05-03 21:11:10.000 7 A 2005-05-03 22:11:10.000 2005-05-03 23:21:10.000*/ Go drop table T
比如臨時表一般可以解決
1 a 2005-5-3 11:31:1 100
2 b 2005-5-3 12:34:1 100
3 a 2005-5-3 18:11:2 101
...由于需要把这些数据存入另外一个表
id 人员帐号 登入时间 登出时间
1 a 2005-5-3 11:31:1 2005-5-3 18:11:2
2 b 2005-5-3 12:34:1 null
...由于原表中的记录的数据有些不正常(登出过程楼记了一部分),所以需要对里面的数据进行判断,比如排序后,如果一个帐号本条记录是登入,下条记录还是登入,那么就把本条记录的登出时间定为下条记录的登出时间。反正需要对数据进行校正。
--看你的需求,不知道是不是這個意思create table T(id int identity(1,1),userid varchar(10),[date] datetime,flag int)
insert into T select 'A','2005-5-3 11:31:10',100
insert into T select 'B','2005-5-3 12:34:10',100
insert into T select 'A','2005-5-3 18:11:10',101
insert into T select 'B','2005-5-3 19:11:10',100
insert into T select 'C','2005-5-3 20:31:10',100
insert into T select 'C','2005-5-3 21:11:10',101
insert into T select 'A','2005-5-3 22:11:10',100
insert into T select 'B','2005-5-3 23:11:10',101
insert into T select 'A','2005-5-3 23:21:10',101
GOselect A.id,A.userid, A.[date] as [Login] ,B.[date] as [Logout]
from
(select id,userid,[date],flag
from T
where flag=100) A
left join
(select id,userid,[date],flag
from T
where flag=101) B
on A.userid=B.userid
and B.[id]>A.[id]
and not exists(select 1 from T where userid=A.userid and id>A.id and id<B.id)
/*
id userid login logout
----------------------------------------------------------------
1 A 2005-05-03 11:31:10.000 2005-05-03 18:11:10.000
2 B 2005-05-03 12:34:10.000 NULL
4 B 2005-05-03 19:11:10.000 2005-05-03 23:11:10.000
5 C 2005-05-03 20:31:10.000 2005-05-03 21:11:10.000
7 A 2005-05-03 22:11:10.000 2005-05-03 23:21:10.000*/
Go
drop table T