匹配进出
0006Login2007-07-04 14:14:06.687
0006Login2007-07-04 14:38:09.267
0006Logout2007-07-04 14:39:55.720
0006Login2007-07-04 14:41:26.080
0006Login2007-07-04 15:10:40.297
0006Login 2007-07-04 15:10:44.937
0006Login 2007-07-04 15:11:48.987
0006Logout2007-07-04 15:12:31.687
0006Login2007-07-04 15:13:12.343
------------------------------------------------
最终目的是生成一个进出表
logout 配对最近的一个login
id login logout
00062007-07-04 14:14:06.687 null
0006 2007-07-04 14:38:09.267 2007-07-04 14:39:55.720
00062007-07-04 14:41:26.080 null
00062007-07-04 15:10:40.297 null
00062007-07-04 15:10:44.937 null
00062007-07-04 15:11:48.987 2007-07-04 15:12:31.687
00062007-07-04 15:13:12.343 null
谢谢大家
[解决办法]
declare @t table(id varchar(10),type varchar(10),date datetime)
insert into @t select '0006 ',rtrim( 'Login '), '2007-07-04 14:14:06.687 '
insert into @t select '0006 ',rtrim( 'Login '), '2007-07-04 14:38:09.267 '
insert into @t select '0006 ',rtrim( 'Logout '), '2007-07-04 14:39:55.720 '
insert into @t select '0006 ',rtrim( 'Login '), '2007-07-04 14:41:26.080 '
insert into @t select '0006 ',rtrim( 'Login '), '2007-07-04 15:10:40.297 '
insert into @t select '0006 ',rtrim( 'Login '), '2007-07-04 15:10:44.937 '
insert into @t select '0006 ',rtrim( 'Login '), '2007-07-04 15:11:48.987 '
insert into @t select '0006 ',rtrim( 'Logout '), '2007-07-04 15:12:31.687 '
insert into @t select '0006 ',rtrim( 'Login '), '2007-07-04 15:13:12.343 '
select
a.id,a.date as login,b.date as logout
from
@t a,@t b
where
a.id=b.id
and
a.type= 'Login '
and
b.type= 'Logout '
and
a.date <b.date
and
not exists(select * from @t where type= 'Login ' and date> a.date and date <b.date)
union
select
a.id,a.date,null
from
@t a,@t b
where
a.id=b.id
and
a.type= 'Login '
and
b.type= 'Logout '
and
a.date <b.date
and
exists(select * from @t where type= 'Login ' and date> a.date and date <b.date)
/*
id login logout
---------- ----------------------- -----------------------
0006 2007-07-04 14:14:06.687 NULL
0006 2007-07-04 14:38:09.267 NULL
0006 2007-07-04 14:38:09.267 2007-07-04 14:39:55.720
0006 2007-07-04 14:41:26.080 NULL
0006 2007-07-04 15:10:40.297 NULL
0006 2007-07-04 15:10:44.937 NULL
0006 2007-07-04 15:11:48.987 2007-07-04 15:12:31.687
*/
[解决办法]
create table #(id varchar(4),type varchar(10),time datetime)
insert into # select '0006 ', 'Login ', '2007-07-04 14:14:06.687 '
union all select '0006 ', 'Login ', '2007-07-04 14:38:09.267 '
union all select '0006 ', 'Logout ', '2007-07-04 14:39:55.720 '
union all select '0006 ', 'Login ', '2007-07-04 14:41:26.080 '
union all select '0006 ', 'Login ', '2007-07-04 15:10:40.297 '
union all select '0006 ', 'Login ', '2007-07-04 15:10:44.937 '
union all select '0006 ', 'Login ', '2007-07-04 15:11:48.987 '
union all select '0006 ', 'Logout ', '2007-07-04 15:12:31.687 '
union all select '0006 ', 'Login ', '2007-07-04 15:13:12.343 '
select id,time[login],
(select time from(select top 1 * from # b where b.time> a.time order by time)a where type= 'logout ')
from # a where type= 'Login '
id login
---- ------------------------------------------------------ --------------------------
0006 2007-07-04 14:14:06.687 NULL
0006 2007-07-04 14:38:09.267 2007-07-04 14:39:55.720
0006 2007-07-04 14:41:26.080 NULL
0006 2007-07-04 15:10:40.297 NULL
0006 2007-07-04 15:10:44.937 NULL
0006 2007-07-04 15:11:48.987 2007-07-04 15:12:31.687
0006 2007-07-04 15:13:12.343 NULL
（所影响的行数为 7 行）
