.Net 面试题之 查询两个时间差

创建表格添加数据

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 

 

 

转载于:https://www.cnblogs.com/hzy168/archive/2013/03/22/2976456.html