相同间隔分组

最新推荐文章于 2022-02-07 11:52:28 发布

CT_LXL

最新推荐文章于 2022-02-07 11:52:28 发布

阅读量596

点赞数

本文链接：https://blog.csdn.net/zlloct/article/details/17285319

版权

WITH test AS(SELECT 1 ID FROM dual UNION ALL
 SELECT 2 ID FROM dual UNION ALL
SELECT 5 ID FROM dual UNION ALL
SELECT 6 ID FROM dual UNION ALL
SELECT 7 ID FROM dual UNION ALL
SELECT 9 ID FROM dual UNION ALL
SELECT 10 ID FROM dual UNION ALL
SELECT 23 ID FROM dual UNION ALL
SELECT 24 ID FROM dual UNION ALL
SELECT 25 ID FROM dual UNION ALL
SELECT 26 ID FROM dual UNION ALL
SELECT 30 ID FROM dual UNION ALL
SELECT 31 ID FROM dual UNION ALL
SELECT 34 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) + 1,
                                      T.ID,
                                      T.ID - 1,
                                      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;