表中只有一个时间字段，如何拆成开始和结束时间

Question

一张表有用户名，时间，类型(进入或退出)这3个字段 （0进入1退出）
username      times                  type
A            2012-8-4 12:10:34        0   
B            2012-8-4 12:15:34        0
A            2012-8-4 13:10:34        1  
B            2012-8-4 14:15:34        1
A            2012-8-4 14:10:34        0  怎么把他做成
username           timein              timeout 
A            2012-8-4 12:10:34       2012-8-4 13:10:34 
B            2012-8-4 12:15:34       2012-8-4 14:15:34
A            2012-8-4 14:10:34                           
TIMEOUT 没有值说明到当前时间点还在线   主要想统计每小时有多少人在线

Answer 1

SQL> select t1.username,to_char(t1.times,'yyyy-mm-dd hh24:mi:ss') timein,
  2   to_char(t2.times,'yyyy-mm-dd hh24:mi:ss') timeout
  3  from
  4  (select row_number() over(partition by username order by username) rn,
  5   username,times from t where type=0) t1
  6  left join
  7  (select row_number() over(partition by username order by username) rn,
  8   username,times from t where type=1) t2
  9  on t1.rn=t2.rn and t1.username=t2.username
 10  order by t1.times;US TIMEIN                                 TIMEOUT                               
-- -------------------------------------- --------------------------------------
A  2012-08-04 12:10:34                    2012-08-04 13:10:34                   
B  2012-08-04 12:15:34                    2012-08-04 14:15:34                   
A  2012-08-04 14:10:34                                                          SQL> spool off;

Answer 2

再把需求说详细点！可以同一个用户一段时间多次进入？还是同一个用户，状态进入以后得下一个状态必然是退出。
如果是这样select t1.username,to_char(t1.times,'yyyy-mm-dd hh24:mi:ss') timein,
  2      to_char(t2.times,'yyyy-mm-dd hh24:mi:ss') timeout
  3  from
  4  (select row_number() over(partition by username order by username) rn,
  5      username,times from t where type=0) t1
  6  left join
  7  (select row_number() over(partition by username order by username) rn,
  8      username,times from t where type=1) t2
  9  on t1.rn=t2.rn and t1.username=t2.username
 10  order by t1.times;US TIMEIN                                 TIMEOUT                               
-- -------------------------------------- --------------------------------------
A  2012-08-04 12:10:34                    2012-08-04 13:10:34                   
B  2012-08-04 12:15:34                    2012-08-04 14:15:34                   
A  2012-08-04 14:10:34                                              
应该可以满足要求。
并不会存在一个用户有很多数据的时候会把进入时间当成结束时间 的情况

Answer 3

--准备数据--
CREATE TABLE usern(uname VARCHAR2(20),times DATE,typ NUMBER);
INSERT INTO usern VALUES('A',to_date('2012-8-4 12:10:34','yyyy-mm-dd hh24:mi:ss'),0);
INSERT INTO usern VALUES('B',to_date('2012-8-4 12:15:34','yyyy-mm-dd hh24:mi:ss'),0);
INSERT INTO usern VALUES('A',to_date('2012-8-4 13:10:34','yyyy-mm-dd hh24:mi:ss'),1);
INSERT INTO usern VALUES('B',to_date('2012-8-4 14:15:34','yyyy-mm-dd hh24:mi:ss'),1);
INSERT INTO usern VALUES('A',to_date('2012-8-4 14:10:34','yyyy-mm-dd hh24:mi:ss'),0);
--思路：
--1、看展现结果，有三个字段（其中两个是表中原有，另一个通过逻辑判断出来），在线时间和离线时间，与用户名息息相关，可知通过用户名可以可到这两个字段。---2、先找出用户的在线时间，当类型为0的时候就是在线时间，那么sql如下：
SELECT  a.uname username ,a.times timein
FROM usern a
WHERE a.typ=0
--3、离线时间为1，sql如下
SELECT  a.uname username ,a.times timeouts
FROM usern a
WHERE a.typ=1--4、在线和离线时间都分别求得了，那么通过用户名整合，把上两个查询结果当成一张表，便可得到用户的在线和离线时间，通过结果观察，离线时间须大于在线时间，那么须对离线时间进行判断，当它大于在线时间时才显示，其他情况显示为空，如下：case when then end子句。整个sql如下：
SELECT b.Username,
       b.Timein,
       CASE  WHEN c.Timeouts>b.Timein THEN c.Timeouts ELSE NULL  END Timeouts         
  FROM (SELECT a.Uname Username, a.Times Timein
          FROM Usern a
         WHERE a.Typ = 0) b,
       (SELECT a.Uname Username, a.Times Timeouts
          FROM Usern a
         WHERE a.Typ = 1) c
 WHERE b.Username = c.Username
 
 
 
 ---统计每小时有多少人在线
 
 --思路：这个就简单了，用户的离线时间为空就表明当前还在线
 --1、通过上面的sql知道哪个用户离线时间为空,求得离线时间为空的用户
 
 SELECT  count(d.Username)--在线人数统计
 FROM (SELECT b.Username,
       b.Timein,
       CASE  WHEN c.Timeouts>b.Timein THEN c.Timeouts ELSE NULL  END Timeouts         
  FROM (SELECT a.Uname Username, a.Times Timein
          FROM Usern a
         WHERE a.Typ = 0) b,
       (SELECT a.Uname Username, a.Times Timeouts
          FROM Usern a
         WHERE a.Typ = 1) c
 WHERE b.Username = c.Username
 )d
 WHERE d.Timeouts IS NULL
 

Answer 4

接上：改动一下sql
以下是求每个时在线人数。
 SELECT to_char(d.timein,'hh24'), count(d.Username)--在线人数统计
 FROM (SELECT b.Username,
       b.Timein,
       CASE  WHEN c.Timeouts>b.Timein THEN c.Timeouts ELSE NULL  END Timeouts         
  FROM (SELECT a.Uname Username, a.Times Timein
          FROM Usern a
         WHERE a.Typ = 0) b,
       (SELECT a.Uname Username, a.Times Timeouts
          FROM Usern a
         WHERE a.Typ = 1) c
 WHERE b.Username = c.Username
 )d
 WHERE d.Timeouts IS NULL
 GROUP BY to_char(d.timein,'hh24')
 

Answer 5

with t as 
(
     select 'A' username,'2012-8-4 12:10:34' times,0 type from dual
     union all
     select 'B','2012-8-4 12:15:34',0 from dual
     union all
     select 'A','2012-8-4 13:10:34',1 from dual
     union all
     select 'B','2012-8-4 14:15:34',1 from dual
     union all
     select 'A','2012-8-4 14:10:34',0 from dual
)
select t1.username,t1.times,t2.times,t1.type,t2.type from(
select username,times,type,row_number() over(partition by username order by username,times) gid from (
select * from t where type=0)) t1
left join 
(select username,times,type,row_number() over(partition by username order by username,times) gid from (
select * from t where type=1)) t2
on t1.username=t2.username and t1.gid=t2.gid