22,207
社区成员
发帖
与我相关
我的任务
分享
drop table t1
create table T1
(
[user] varchar(30),
operate varchar(10),
time datetime
)
insert into T1
select 'LiMing','Login','2010/10/24 8:03' union all
select 'WangYi','Login','2010/10/24 8:14' union all
select 'WangYi','Logout','2010/10/24 16:14' union all
select 'LiMing','Logout','2010/10/24 16:14'
select [user],
cast(cast(round(interval * 1.0 / 60,0,1) as int) as varchar) + ':' +
case when interval * 1.0 % 60 <> 0
then cast(cast(round(interval * 1.0 % 60,0,1) as int) as varchar)
else '00'
end
from
(
select T1.[user],
DATEDIFF(MINUTE,t1.time,t2.time) as interval
from T1
inner join T1 t2
on t1.[user] = t2.[user]
and t1.operate = 'login'
and t2.operate = 'logout'
)a
/*
user (无列名)
LiMing 8:11
WangYi 8:00
*/
[/quote]
哥哥,你太强大了,经过测试,两种方法答案都对![/quote]
能帮到你就好啊
drop table t1
create table T1
(
[user] varchar(30),
operate varchar(10),
time datetime
)
insert into T1
select 'LiMing','Login','2010/10/24 8:03' union all
select 'WangYi','Login','2010/10/24 8:14' union all
select 'WangYi','Logout','2010/10/24 16:14' union all
select 'LiMing','Logout','2010/10/24 16:14'
select [user],
cast(cast(round(interval * 1.0 / 60,0,1) as int) as varchar) + ':' +
case when interval * 1.0 % 60 <> 0
then cast(cast(round(interval * 1.0 % 60,0,1) as int) as varchar)
else '00'
end
from
(
select T1.[user],
DATEDIFF(MINUTE,t1.time,t2.time) as interval
from T1
inner join T1 t2
on t1.[user] = t2.[user]
and t1.operate = 'login'
and t2.operate = 'logout'
)a
/*
user (无列名)
LiMing 8:11
WangYi 8:00
*/
[/quote]
哥哥,你太强大了,经过测试,两种方法答案都对!
--方法2.
select [user],
convert(varchar(5),DATEADD(MINUTE,interval,time),114)
from
(
select T1.[user],
convert(varchar(10),t1.time,120) as time,
DATEDIFF(MINUTE,t1.time,t2.time) as interval
from T1
inner join T1 t2
on t1.[user] = t2.[user]
and t1.operate = 'login'
and t2.operate = 'logout'
)a
/*
user (无列名)
LiMing 08:11
WangYi 08:00
*/
SELECT ta.[User], CONVERT(varchar, DATEADD(s, DATEDIFF(s,ta.[time],tb.time), 0), 108)
From
(select [user], [time] from T1 where [operate] = 'login') as ta
inner join
(select [user], [time] from T1 where [operate] = 'logout') as tb
on ta.[user] = tb.[user]
经过测试
经过我的测试可以了,果然是大神啊!
有秒没有关系。
哈哈!万分感谢!
select cast( DATEDIFF(MINUTE,'2010-10-24 8:13','2010-10-24 16:14') /60 as varchar(2))
+':'+cast ( DATEDIFF(MINUTE,'2010-10-24 8:13','2010-10-24 16:14')%60 as varchar(2))
try this
drop table t1
create table T1
(
[user] varchar(30),
operate varchar(10),
time datetime
)
insert into T1
select 'LiMing','Login','2010/10/24 8:03' union all
select 'WangYi','Login','2010/10/24 8:14' union all
select 'WangYi','Logout','2010/10/24 16:14' union all
select 'LiMing','Logout','2010/10/24 16:14'
select [user],
cast(cast(round(interval * 1.0 / 60,0,1) as int) as varchar) + ':' +
case when interval * 1.0 % 60 <> 0
then cast(cast(round(interval * 1.0 % 60,0,1) as int) as varchar)
else '00'
end
from
(
select T1.[user],
DATEDIFF(MINUTE,t1.time,t2.time) as interval
from T1
inner join T1 t2
on t1.[user] = t2.[user]
and t1.operate = 'login'
and t2.operate = 'logout'
)a
/*
user (无列名)
LiMing 8:11
WangYi 8:00
*/
SELECT ta.UserName, CONVERT(varchar, DATEADD(s, DATEDIFF(s,ta.timee,tb.time), 0), 108)
From
(select [user], [time] from T1 where [operate] = 'login') as ta
inner join
(select [user], [time] from T1 where [operate] = 'logout') as tb
on ta.[user] = tb.[user]
将时间差转为秒数计算,然后通过格式化这样应该能得出 HH:mm:ss 格式,然后考虑怎么去掉秒数ss就可以了