我想得到用户在线时长,格式是:08:00和08:43这种格式的在线时长结果。我自己尝试查了sql的文档,也百度了很多。但是没有这方面的应用。
我自己也尝试写了很多但是不行。
我只能得到在线的总分钟数,或者总秒数。无法弄成想要的格式。
这是我的代码:
select ta.[user],(DATEDIFF(mi,ta.time,tb.time)) from
(select * from T1 where T1.operate='Login') as ta
inner join
(select * from T1 where T1.operate='Logout') as tbon ta.[user]=tb.[user]------------------------------------
或者这样写:select ta.[user], cast(datediff(hour, ta.time, tb.time) as varchar) + ':' + cast(DATEDIFF(MINUTE, ta.time, tb.time) as varchar)
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];两种写法都无法实现想要的结果。请求大神指导下,帮忙给出一种解决方法。我测试完,发效果图。sql
我自己也尝试写了很多但是不行。
我只能得到在线的总分钟数,或者总秒数。无法弄成想要的格式。
这是我的代码:
select ta.[user],(DATEDIFF(mi,ta.time,tb.time)) from
(select * from T1 where T1.operate='Login') as ta
inner join
(select * from T1 where T1.operate='Logout') as tbon ta.[user]=tb.[user]------------------------------------
或者这样写:select ta.[user], cast(datediff(hour, ta.time, tb.time) as varchar) + ':' + cast(DATEDIFF(MINUTE, ta.time, tb.time) as varchar)
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];两种写法都无法实现想要的结果。请求大神指导下,帮忙给出一种解决方法。我测试完,发效果图。sql
解决方案 »
- sql server 2005 *.bak文件要怎么还原
- SQL 查询问题
- 莱鸟问莱鸟级别问题!
- 一个表tb1有字段dt_a,另一个表tb2也有字段dt_a,查询tb1中包含在tb2中dt_a
- 请问如何在Asp中调用如下通用分页储存过程?我调用的时候总说对象没有打开,而实际上已经打开了
- 需要取其他机器数据库的数据,应该用什么技术?
- 如何查出sqlserver2000中被锁住了的表是那几个?怎么解决?
- 怎样可以加快查询速度?
- 急!急!急!关于sql sever2000安装的问题!
- sql特别简单的问题但就是有错误,不明白
- sql先排序再去除重复的问题,在线等
- 急求!!!SQL sever 2008导入TXT文件出现以下错误怎么解决?
或者返回总的秒数。但是我无法将时间转成小时加分钟的格式。如07:30七小时三十分钟等。
只这样不行。
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就可以了
或者返回总的秒数。但是我无法将时间转成小时加分钟的格式。如07:30七小时三十分钟等。
只这样不行。这个是你要的不:drop table t1create 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 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
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]经过测试经过我的测试可以了,果然是大神啊!
有秒没有关系。
哈哈!万分感谢!
-------------------- ------------------------------
LiMing 08:41:00
WangYi 08:00:00(2 行受影响)
这是效果。thank you!
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
*/
或者返回总的秒数。但是我无法将时间转成小时加分钟的格式。如07:30七小时三十分钟等。
只这样不行。这个是你要的不:drop table t1create 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
*/哥哥,你太强大了,经过测试,两种方法答案都对!
或者返回总的秒数。但是我无法将时间转成小时加分钟的格式。如07:30七小时三十分钟等。
只这样不行。这个是你要的不:drop table t1create 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
*/哥哥,你太强大了,经过测试,两种方法答案都对!能帮到你就好啊