[ORACLE] 群組合併 - CONNECT BY應用

本範例將利用CONNECT BY進行相關群組之歸類，如下圖所示，群組AAAA與群組BBBB具有『2』之共同值（Val），因此將所有BBBB群組資料併入AAAA群組，即資料列3、4。CCCC與BBBB群組並無共同值，無法串連合併，但DDDD與CCCC兩者具有『5』之共同值，則資料列7、8併入CCCC群組；而EEEE與DDDD具有『6』之共同值，則EEEE將併入DDDD群組，但先前DDDD已併入CCCC群組，則EEEE也將一同併入CCCC群組，即資料列9、10。

SQL:

SELECT GRP

       , VAL

       , NVL(SUBSTR(GRP_LIST, 2, INSTR(GRP_LIST, ',', 2)-2), GRP) NEW_GRP

      -- , GRP_LIST

FROM

    (

      SELECT A.GRP

             , A.VAL

             , MIN(SYS_CONNECT_BY_PATH(GRP, ',')) GRP_LIST

      FROM

          (     

          SELECT GRP

                 , VAL

                 , ROW_NUMBER() OVER(PARTITION BY GRP ORDER BY VAL) GrpSeq

          FROM TEST A

          ) A

      CONNECT BY (

                   (--組內遞推, 只管大小順序, 不計其值

                    GRP = PRIOR GRP 

                        AND GrpSeq = PRIOR GrpSeq+1                   

                    )

                   OR

                   ( --組間遞推, 值需相同

                     GRP >  PRIOR  GRP

                         AND VAL = PRIOR VAL

                   )

                 )

      GROUP BY GRP, VAL

     )

ORDER BY GRP, VAL

資料:

CREATE TABLE Test

(

GRP VARCHAR2(10),

VAL NUMBER(2)

);

INSERT INTO TEST VALUES ('AAAA', 1);

INSERT INTO TEST VALUES ('AAAA', 2);

INSERT INTO TEST VALUES ('BBBB', 2);

INSERT INTO TEST VALUES ('BBBB', 3);

INSERT INTO TEST VALUES ('CCCC', 4);

INSERT INTO TEST VALUES ('CCCC', 5);

INSERT INTO TEST VALUES ('DDDD', 6);

INSERT INTO TEST VALUES ('DDDD', 5);

INSERT INTO TEST VALUES ('EEEE', 6);

INSERT INTO TEST VALUES ('EEEE', 8);

COMMIT;