22,206
社区成员
发帖
与我相关
我的任务
分享
/*
login之后多条logout,只取最先logout的记录
logout之后多条login, 只取最后login的记录
*/
;with t1 as
(
select id=row_number()over(partition by userId order by times),* from userlog where type='login'
),
t2 as
(
select id=row_number()over(partition by userId order by times),* from userlog where type='logout'
),
t3 as
(
select a.userId, a.times i, b.times o from t1 a, t2 b where a.userId=b.userId and a.times<b.times
and b.times < isnull((select times from t1 where userId=a.userId and id=a.id+1),'9999')
),
t4 as -- 写到这里终于把有效的i/o对应起来
(
select * from t3 t where not exists (select 1 from t3 where userId=t.userId and i=t.i and o<t.o)
)
select userId, sum(datediff(hour,i,o)) from t4 group by userId --该用户一共在线时间
/*
回头一瞄:该用户平均每天在线小时数,最长不上线间隔天数
该问题缺条件,没有时间范围,无法计算,难道从1753年计算到9999年?
*/
SELECT TB1.ID,TB1.NAME,ISNULL((SELECT TOP 1 NAME FROM TB5 WHERE ID=TB1.ID),’’) ---你确认这边只返回一条,否则会报错,保险起见加上TOP 1
FROM TB1 ,TB2,TB3
WHERE TB1.ID=TB2.PID AND TB1.ID=TB3.PID
AND NOT EXISTS(SELECT NULL FROM TB4 WHERE TB1.ID=ID)
GROUP BY TB1.ID,TB1.NAME
-- 1:
;with tree as
(
select * from @tb where id=@id
union all
select a.* from @tb a join tree b on a.pid=b.id
)
select * from tree
-- 3: tb2,tb3是废的,一点用都没有
select a.id, a.name, isnull(b.name,0) from tb1 a left join tb5 b on a.id=b.id
where not exists (select 1 from tb4 where id=a.id)
group by a.id, a.name
;with cte
as(
select *
from @tb
where id=@id
union all
select a.*
from @tb a,cte b
where a.pid=b.id)
select * from cte
;with cte
as(
select *
from @tb
where id=2
union all
select a.*
from @tb a,cte b
where a.pid=b.id)
select * from cte