请问下兄弟们复杂的在线时长的计算问题
要求计算出每个小时的所有用户的在线时长和登陆次数,不管这个小时有没有人登陆过,不用管天。

USERID LOGINTIME LOGOUTTIME
AA 2010-03-05 00:01:23 2010-03-05 02:04:21
AA 2010-03-05 02:07:01 2010-03-05 03:01:03
BB 2010-03-05 23:55:03 2010-03-06 00:03:01

需要得到以下结果
DES TIMES COUNTS
0点~1点 62 2
1点~2点 60 1
2点~3点 57 1
.
.
23点~24点5 1


------解决方案--------------------
select t1.begin_time,t1.end_time,
sum(case when t1.end_time>t2.logouttime then t2.logouttime
when t1.end_tme <= t2.logouttime then t1.end_time end
-
case when t1.begin_time > t2.logintime then t1.begin_time
when t1.begin_time <= t2.logintime ten t2.logintime end ),
count(t1.begin_time) c1 

from
(
select rownum -1 begin_time, rownum end_time
from dual 
connect by rownum <= 24
) t1,
tablename t2
where t1.begin_time between to_number(to_char(t2.logintime(+),'HH24')) 
and to_number(to_char(t2.logouttime(+),'HH24')) 
or t1.end_time between to_number(to_char(t2.logintime(+),'HH24')) 
and to_number(to_char(t2.logouttime(+),'HH24'))
------解决方案--------------------
参考语句：
select to_char(t1.begin_time) || '点~' || to_char(t1.end_time) || '点' period,t2.total,t2.c1
from (
select rownum -1 begin_time, rownum end_time
from dual
connect by rownum <= 24
) t1,
( select t1.begin_time, to_char(t1.begin_time) || '点~' || to_char(t1.end_time) || '点' period,
sum(case when t1.end_time>t2.logouttime then t2.logouttime
when t1.end_time <= t2.logouttime then t1.end_time end
-
case when t1.begin_time > t2.logintime then t1.begin_time
when t1.begin_time <= t2.logintime then t2.logintime end ) total,
count(t1.begin_time) c1

from
(
select rownum -1 begin_time, rownum end_time
from dual
connect by rownum <= 24
) t1,
(select to_number(to_char(t2.logintime,'HH24'))logintime,
to_number(to_char(t2.logouttime,'HH24')) logouttime
 from logtab t2) t2
 where (t1.begin_time between t2.logintime and t2.logouttime)
 or (t1.end_time between t2.logintime and t2.logouttime)
 group by t1.begin_time,to_char(t1.begin_time) || '点~' || to_char(t1.end_time) || '点') t2
 where t1.begin_time = t2.begin_time(+)