create table T1
(
ID INTEGER,
LOGTIME DATE,
OLD_STATE CHAR(10),
NET_STATE CHAR(10)
)
insert into T1 (ID, LOGTIME, OLD_STATE, NET_STATE)
values ('2293912', to_date('11-09-2015 08:42:00', 'dd-mm-yyyy hh24:mi:ss'), '40 ', '4X ');
insert into T1 (ID, LOGTIME, OLD_STATE, NET_STATE)
values ('2293912', to_date('11-09-2015 09:09:00', 'dd-mm-yyyy hh24:mi:ss'), '4X ', '21 ');
insert into T1 (ID, LOGTIME, OLD_STATE, NET_STATE)
values ('2293912', to_date('14-09-2015 07:55:00', 'dd-mm-yyyy hh24:mi:ss'), '40 ', '4X ');
insert into T1 (ID, LOGTIME, OLD_STATE, NET_STATE)
values ('2293912', to_date('14-09-2015 08:04:00', 'dd-mm-yyyy hh24:mi:ss'), '4X ', '50 ');
insert into T1 (ID, LOGTIME, OLD_STATE, NET_STATE)
values ('2297220', to_date('24-09-2015 08:17:00', 'dd-mm-yyyy hh24:mi:ss'), '40 ', '4X ');
insert into T1 (ID, LOGTIME, OLD_STATE, NET_STATE)
values ('2297220', to_date('24-09-2015 09:32:00', 'dd-mm-yyyy hh24:mi:ss'), '4X ', '50 ');
insert into T1 (ID, LOGTIME, OLD_STATE, NET_STATE)
values ('2297565', to_date('09-10-2015 11:41:00', 'dd-mm-yyyy hh24:mi:ss'), '40 ', '4X ');
insert into T1 (ID, LOGTIME, OLD_STATE, NET_STATE)
values ('2297565', to_date('09-10-2015 14:54:00', 'dd-mm-yyyy hh24:mi:ss'), '4X ', '50 ');
所有数据都是成对出现的
结果要求：计算出相同的applyid下 old_state='4X'的logtime 减去 new_state='4X'的时间差
结果示例：
ID LOGTIME OLD_STATE NET_STATE result
2293912 2015/9/11 8:42:00 40 4X
2293912 2015/9/11 9:09:00 4X 21 0.01875
2293912 2015/9/14 7:55:00 40 4X
2293912 2015/9/14 8:04:00 4X 50 0.00625
2297220 2015/9/24 8:17:00 40 4X
2297220 2015/9/24 9:32:00 4X 50 0.052083
2297565 2015/10/9 11:41:00 40 4X
2297565 2015/10/9 14:54:00 4X 50 0.134028

@xulongxc 将此表分别作表A，表B。查询时分别在表前加上rownum，然后使用括号打包成表A1, 表B1。然后通过对rownum列使用mod取余数来得出奇数行表A2和偶数行表B2，然后表A2和B2进行同applyid连接，查询后可将A2的LOGTIME和B2的LOGTIME差得出。得出的差值列和B2的行列和A2的行列进行union all即可。

怎么写