关于ORACLE的查询语句

Question

有这样一个设备状态历史表：
序列号ID｜设备号｜源状态｜目标状态｜目标状态开始时间｜源状态开始时间
001     ｜  A1  ｜ RUN  ｜  WAIT  ｜2012-8-1 23：30 ｜2012-8-1 08：30  |  
002     ｜  A1  ｜ WAIT ｜  IDLE  ｜2012-8-2 10：30 ｜2012-8-1 23：30  | 
003     ｜  A1  ｜ IDLE ｜  RUN   ｜2012-8-2 19：30 ｜2012-8-2 10：30  | 
004     ｜  A1  ｜ RUN  ｜  IDLE  ｜2012-8-3 12：30 ｜2012-8-2 19：30  |
005     ｜  A2  ｜ RUN  ｜  WAIT  ｜2012-8-1 20：30 ｜2012-8-1 08：30  |  
006     ｜  A2  ｜ WAIT ｜  IDLE  ｜2012-8-2 11：30 ｜2012-8-1 20：30  | 
007     ｜  A2  ｜ IDLE ｜  RUN   ｜2012-8-2 18：30 ｜2012-8-2 11：30  | 
008     ｜  A2  ｜ RUN  ｜  IDLE  ｜2012-8-3 12：30 ｜2012-8-2 18：30  |这个表的作用是这样，当一个设备的状态改变时，就会在这个表里面插入一条数据，
记录一下这个设备由什么状态改为什么状态。
其中表的“源状态开始时间”是等于对应的设备的上一个状态的“目标状态开始时间”我的困难是：
现在需要查询：用户输入一段时间，例：2012-8-2 00：00至2012-9-4 00：00
在这段时间内，各个设备处在每一种状态下的时长。难点：像A1这台设备，
最后的状态（目标状态）是IDLE，
这样状态的开始时间（目标状态开始时间）是：2012-8-3 12：30
也就是说A1这个设备从2012-8-3 12：30到现在都是处于IDLE状态。
那么A1处于IDLE状态的时间就应该是历史内统计的时间再加上从2012-8-3 12：30到现在的时间。我怎么去组织这样的查询语句？请高手指点迷津。

Answer 1

--给你补了一条数据进去，条件让我改成8月1日0点了，不然看着怪怪的。
[SYS@myoracle] SQL>WITH T AS
  2   (SELECT '001' ID,
  3           'A1' NAME,
  4           'RUN ' F_STATUS,
  5           'WAIT' T_STATUS,
  6           TO_DATE('2012-8-1 23:30', 'yyyy-mm-dd hh24:mi') T_TIME,
  7           TO_DATE('2012-8-1 08:30', 'yyyy-mm-dd hh24:mi') F_TIME
  8      FROM DUAL
  9    UNION ALL
 10    SELECT '002' ID,
 11           'A1' NAME,
 12           'WAIT' F_STATUS,
 13           'IDLE' T_STATUS,
 14           TO_DATE('2012-8-2 10:30', 'yyyy-mm-dd hh24:mi') T_TIME,
 15           TO_DATE('2012-8-1 23:30', 'yyyy-mm-dd hh24:mi') F_TIME
 16      FROM DUAL
 17    UNION ALL
 18    SELECT '003' ID,
 19           'A1' NAME,
 20           'IDLE' F_STATUS,
 21           'RUN ' T_STATUS,
 22           TO_DATE('2012-8-2 19:30', 'yyyy-mm-dd hh24:mi') T_TIME,
 23           TO_DATE('2012-8-2 10:30', 'yyyy-mm-dd hh24:mi') F_TIME
 24      FROM DUAL
 25    UNION ALL
 26    SELECT '004' ID,
 27           'A1' NAME,
 28           'RUN ' F_STATUS,
 29           'IDLE' T_STATUS,
 30           TO_DATE('2012-8-3 12:30', 'yyyy-mm-dd hh24:mi') T_TIME,
 31           TO_DATE('2012-8-2 19:30', 'yyyy-mm-dd hh24:mi') F_TIME
 32      FROM DUAL
 33    UNION ALL
 34    SELECT '005' ID,
 35           'A2' NAME,
 36           'RUN ' F_STATUS,
 37           'WAIT' T_STATUS,
 38           TO_DATE('2012-8-1 20:30', 'yyyy-mm-dd hh24:mi') T_TIME,
 39           TO_DATE('2012-8-1 08:30', 'yyyy-mm-dd hh24:mi') F_TIME
 40      FROM DUAL
 41    UNION ALL
 42    SELECT '006' ID,
 43           'A2' NAME,
 44           'WAIT' F_STATUS,
 45           'IDLE' T_STATUS,
 46           TO_DATE('2012-8-2 11:30', 'yyyy-mm-dd hh24:mi') T_TIME,
 47           TO_DATE('2012-8-1 20:30', 'yyyy-mm-dd hh24:mi') F_TIME
 48      FROM DUAL
 49    UNION ALL
 50    SELECT '007' ID,
 51           'A2' NAME,
 52           'IDLE' F_STATUS,
 53           'RUN ' T_STATUS,
 54           TO_DATE('2012-8-2 18:30', 'yyyy-mm-dd hh24:mi') T_TIME,
 55           TO_DATE('2012-8-2 11:30', 'yyyy-mm-dd hh24:mi') F_TIME
 56      FROM DUAL
 57    UNION ALL
 58    SELECT '008' ID,
 59           'A2' NAME,
 60           'RUN ' F_STATUS,
 61           'IDLE' T_STATUS,
 62           TO_DATE('2012-8-3 12:30', 'yyyy-mm-dd hh24:mi') T_TIME,
 63           TO_DATE('2012-8-2 18:30', 'yyyy-mm-dd hh24:mi') F_TIME
 64      FROM DUAL),
 65  T2 AS
 66   (SELECT ID, NAME, F_STATUS, T_STATUS, F_TIME, T_TIME
 67      FROM T
 68    UNION
 69    SELECT TRIM(TO_CHAR(ID + 1, '099')), NAME, T_STATUS, '-', T_TIME, SYSDATE
 70      FROM T
 71     WHERE CONNECT_BY_ISLEAF = 1
 72    CONNECT BY NOCYCLE PRIOR NAME = NAME
 73           AND PRIOR T_TIME = F_TIME)
 74  SELECT NAME, F_STATUS, SUM(T_TIME - F_TIME)
 75    FROM T2
 76   WHERE F_TIME BETWEEN TO_DATE('2012-8-1 00:00', 'yyyy-mm-dd hh24:mi') AND
 77         TO_DATE('2012-9-4 00:00', 'yyyy-mm-dd hh24:mi')
 78   GROUP BY NAME, F_STATUS
 79   ORDER BY NAME;NA F_ST SUM(T_TIME-F_TIME)
-- ---- ------------------
A1 IDLE         33.5534028
A1 RUN          1.33333333
A1 WAIT         .458333333
A2 IDLE         33.4700694
A2 RUN                1.25
A2 WAIT               .625已选择6行。[SYS@myoracle] SQL>