创建表格添加数据
create table T ( username varchar(20), operate varchar(20), time datetime ) insert into T(username,operate,time) values ('LiMing','Login','2010/10/24 8:03') insert into T(username,operate,time) values ('WangYi','Login','2010/10/24 8:14') insert into T(username,operate,time) values ('LiMing','Logout','2010/10/24 16:14') insert into T(username,operate,time) values ('WangYi','Logout','2011/10/24 16:44')
表字连接 用case
select distinct a.username ,上机时间=case when a.time>b.time then substring(cast(a.time-b.time as varchar(50)),5,12) when b.time>a.time then substring(cast(b.time-a.time as varchar(50)),5,12) else '0:00' end from T as a inner join T as b on a.username=b.username and a.operate<>b.operate
另一种方法
1 select a.username,cast(datediff(yy,a.time,b.time) as varchar(10))+':'+ 2 cast(datediff(mm,a.time,b.time)%12 as varchar(10))+':'+ 3 cast(datediff(dd,a.time,b.time)%365 as varchar(10))+' '+ 4 cast(datediff(hh,a.time,b.time)%12 as varchar(10))+':'+ 5 cast(datediff(mi,a.time,b.time)%60 as varchar(10)) as 上机时间 6 7 from (select * from T where T .operate ='Login' ) as a 8 inner join (select * from T where T.operate ='Logout' ) as b 9 10 on a.username = b.username