一些比较难的sql问题：1(四)
on all
select 2,'55,6,99,87,1000' union all
select 3,'7,567567,567,43,123' union ALL
SELECT 4,'2,34,45'
--------------开始查询--------------------------
DECLARE @s VARCHAR(1000)
SET @s= '2,34,45,345,867,4,984'
;with t
as
(
select t.ID,
t.IndexArr,
f.col,
--把IndexArr按照分隔符,拆分成了多少个字符串
COUNT(*) over(PARTITION by IndexArr) as split_str_count
from tb t
cross apply dbo.f_splitSTR(t.IndexArr,',') f
)
select t.ID,
t.IndexArr
from t
where charindex(col, ','+@s+',') > 0
group by t.ID,
t.IndexArr,
t.split_str_count
having COUNT(*) = t.split_str_count --比如2,34,45分拆为3个字符串，
--那么在经过where条件过滤后，记录数也必须是3
--这样说明了indexarr中的字符串都在@s变量中出现了
6、如何解决用户在线登陆时间——的小时和分钟计算问题。
我想得到用户在线时长，格式是：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 tb
on 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];
两种写法都无法实现想要的结果。请求大神指导下，帮忙给出一种解决方法。
我测试完，发效果图。
我的解法：
方法1：
[sql]
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
*/
方法2：
[sql]
--方法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
*/