日期:2014-05-17 浏览次数:20895 次
--给你补了一条数据进去,条件让我改成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