解决方案 »
- oracle常用的应用语句有那些大家给提供下如:建库,建表,备份,恢复
- 请问有没有命令可以配置oracle数据库的服务名?
- 如何在oracle存储过程中使用partiton 啊,想用for循环实现
- 100分急求解决方案
- sql server 与oracle 的一条语句??
- oracle新建数据库问题
- 存储过程中执行动态SQL的问题
- 求教,在oracle9i中,如何创建一个新的实例啊?(搜索过以前的,没有)在线等。。。
- 谁有比较详细的建立快照复制的资料???????????????????????????????????
- 如何将大容量数据分片处理和保存?
- 二个表关联,求高效的sql写法
- 一道数据库题,!!高手请帮忙,给分
from (
select UserID,LogInTime,LogOutTime,OnlineTime,SessionID,row_number()over(partition by SessionID order by OnlineTime desc) rn
from table_name )
where rn=1
group by userid
(select UserID,(to_date(max(LogOutTime),'yyyy-mm-dd hh24:mi:ss')-to_date(min(LogInTime),'yyyy-mm-dd hh24:mi:ss'))*24*60 tm,SessionId from tb group by SessionId,UserID)
group by UserID order by UserID;
from (select userid,max(logouttime)-min(logintime) onlinetime from tt group by userid,sessionid)
group by userid
---------- -------------------- -------------------- ---------- ----
1 2009-11-01 10:15:00 2009-11-01 10:30:00 15 1001
2 2009-11-01 10:16:00 2009-11-01 10:18:00 2 1002
3 2009-11-01 10:17:00 2009-11-01 10:19:00 2 1003
1 2009-11-01 10:20:00 2009-11-01 10:50:00 10 1001
1 2009-11-02 10:20:00 2009-11-02 10:25:00 5 1004
3 2009-11-02 09:00:00 2009-11-02 09:10:00 10 1005
1 2009-11-03 10:00:00 2009-11-03 10:01:00 1 1006已选择7行。已用时间: 00: 00: 00.00
11:02:32 scott@TUNGKONG> select UserID,sum(tm) from
11:02:42 2 (select UserID,(to_date(max(LogOutTime),'yyyy-mm-dd hh24:mi:ss')-to_date(min(LogInTime),'yyyy-mm-dd hh24:mi:ss'))*24*60 tm,SessionId from tb group by SessionId,UserID)
11:02:42 3 group by UserID order by UserID; USERID SUM(TM)
---------- ----------
1 41
2 2
3 12已用时间: 00: 00: 00.01
(
select 1 UserID, '2009-11-01 10:15:00' LogInTime,'2009-11-01 10:50:00' LogOutTime,35 OnlineTime,1001 SessionID from dual
union all
select 2, '2009-11-01 10:16:00', '2009-11-01 10:18:00', 2, 1002 from dual
union all
select 3, '2009-11-01 10:17:00', '2009-11-01 10:19:00', 2, 1003 from dual
union all
select 1, '2009-11-01 10:20:00', '2009-11-01 10:50:00', 10, 1001 from dual
union all
select 1, '2009-11-02 10:20:00', '2009-11-02 10:25:00', 5, 1004 from dual
union all
select 3, '2009-11-02 09:00:00', '2009-11-02 09:10:00', 10, 1005 from dual
union all
select 1, '2009-11-03 10:00:00', '2009-11-03 10:01:00', 1, 1006 from dual
)
select s.UserID,sum(ontime) from
(select tt.sessionid,round(to_number(max(to_date(tt.LogOutTime,'yyyy-DD-MM HH24:MI:SS'))- min(to_date(tt.LogInTime,'yyyy-DD-MM HH24:MI:SS')))*24*60) ontime,max(tt.UserID) UserID
from tt
group by tt.sessionid) s
group by s.UserID
结果
1 41
2 2
3 12
应该是按照sessionid来选择最大和最小
然后再sum
楼主的理解有问题吧?
就是按照sessionid来选择最大和最小,然后再sum的啊你应该自己测试一下。