日期:2014-05-17 浏览次数:20975 次
WITH test AS(SELECT 141047 ID FROM dual UNION ALL
SELECT 142047 ID FROM dual UNION ALL
SELECT 143047 ID FROM dual UNION ALL
SELECT 144047 ID FROM dual UNION ALL
SELECT 153047 ID FROM dual UNION ALL
SELECT 154047 ID FROM dual UNION ALL
SELECT 155047 ID FROM dual)
SELECT T2.ID STARTTIME,
DECODE(SUBSTR(T2.C1, 1, INSTR(T2.C1, ' ')),
NULL,
T2.ID,
SUBSTR(T2.C1, 1, INSTR(T2.C1, ' '))) ENDTIME
FROM (SELECT T1.ID, MAX(T1.C1) C1
FROM (SELECT T.*,
LTRIM(SYS_CONNECT_BY_PATH(T.ID, ' '), ' ') C1,
CONNECT_BY_ISLEAF RN
FROM (SELECT T.ID,
DECODE(LAG(T.ID) OVER(ORDER BY T.ID) + 1000,
T.ID,
T.ID - 1000,
NULL) PID
FROM TEST T
ORDER BY T.ID) T
CONNECT BY T.ID = PRIOR T.PID) T1
WHERE T1.RN = 1
GROUP BY T1.ID) T2
ORDER BY T2.ID;