declare @t table(userid int,actionname varchar(10),actiontime datetime)
insert into @t select 1,'login' ,'2009-7-1 14:01:00'
insert into @t select 1,'logout','2009-7-1 14:02:00'
insert into @t select 1,'login' ,'2009-7-1 14:10:00'
insert into @t select 1,'login' ,'2009-7-1 14:11:00'
insert into @t select 1,'logout','2009-7-1 14:13:00'
insert into @t select 2,'login' ,'2009-7-1 14:20:00'
insert into @t select 2,'logout','2009-7-1 14:25:00' select
b.userid,max(a.actiontime) as login,b.actiontime as logout
from
(select
t.*
from
@t t
where
t.actionname='login'
and
t.actionname!=isnull((select
top 1 actionname
from
@t
where
userid=t.userid and actiontime<t.actiontime
order by
actiontime desc),'')) a, @t b
where
a.userid=b.userid and b.actionname='logout' and a.actiontime<b.actiontime
group by
b.userid,b.actiontime/*
userid login logout
----------- ------------------------------------------------------ ------------------------------------------------------
1 2009-07-01 14:01:00.000 2009-07-01 14:02:00.000
1 2009-07-01 14:10:00.000 2009-07-01 14:13:00.000
2 2009-07-01 14:20:00.000 2009-07-01 14:25:00.000
*/
insert into @t select 1,'login' ,'2009-7-1 14:01:00'
insert into @t select 1,'logout','2009-7-1 14:02:00'
insert into @t select 1,'login' ,'2009-7-1 14:10:00'
insert into @t select 1,'login' ,'2009-7-1 14:11:00'
insert into @t select 1,'logout','2009-7-1 14:13:00'
insert into @t select 2,'login' ,'2009-7-1 14:20:00'
insert into @t select 2,'logout','2009-7-1 14:25:00' select
b.userid,max(a.actiontime) as login,b.actiontime as logout
from
(select
t.*
from
@t t
where
t.actionname='login'
and
t.actionname!=isnull((select
top 1 actionname
from
@t
where
userid=t.userid and actiontime<t.actiontime
order by
actiontime desc),'')) a, @t b
where
a.userid=b.userid and b.actionname='logout' and a.actiontime<b.actiontime
group by
b.userid,b.actiontime/*
userid login logout
----------- ------------------------------------------------------ ------------------------------------------------------
1 2009-07-01 14:01:00.000 2009-07-01 14:02:00.000
1 2009-07-01 14:10:00.000 2009-07-01 14:13:00.000
2 2009-07-01 14:20:00.000 2009-07-01 14:25:00.000
*/
不知道这样可以吗?
if object_id('log_user')is not null
drop table log_user
create table log_user(userid int,actionname varchar(100),actiontime datetime)
insert log_user
select 1 , 'login' , '2009-7-1 14:01:00' union all
select 1 , 'logout' , '2009-7-1 14:02:00' union all
select 1 , 'login' , '2009-7-1 14:10:00' union all
select 1 , 'login' , '2009-7-1 14:11:00' union all
select 1 , 'logout' , '2009-7-1 14:13:00' union all
select 2 , 'login' , '2009-7-1 14:20:00' union all
select 2 , 'logout', '2009-7-1 14:25:00'
select userid,
actionname,
max(actiontime)as actiontime into #log_user
from log_user group by userid,actionname
order by userid
select * into #aaa from log_user a where not
exists(select 1 from log_user where actiontime<a.actiontime and actionname=a.actionname and userid=a.userid)
-----select * from #aaa
select userid,
login=max(case when actionname='login' then actiontime else 0 end ),
logout=max(case when actionname='logout' then actiontime else 0 end)
from #aaa group by userid
结贴