匹配进出
0006 Login 2007-07-04   14:14:06.687
0006 Login 2007-07-04   14:38:09.267
0006 Logout 2007-07-04   14:39:55.720
0006 Login 2007-07-04   14:41:26.080
0006 Login 2007-07-04   15:10:40.297
0006 Login   2007-07-04   15:10:44.937
0006 Login   2007-07-04   15:11:48.987
0006 Logout 2007-07-04   15:12:31.687
0006 Login 2007-07-04   15:13:12.343
------------------------------------------------
最终目的是生成一个进出表
logout   配对最近的一个login
id               login                                             logout
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

谢谢大家

------解决方案--------------------
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
*/

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