群組合併 - With指令應用

本範例將利用一般表格運算式（Common Table Expression：CTE）即WITH指令，進行相關群組之歸類，如下圖所示，群組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。

註：以下為MSSQL指令，ORACLE只需將#字元剔除即可執行。 

WITH Org

AS

  (

   SELECT GRP

         , VAL

         , ROW_NUMBER() OVER(ORDER BY GRP, VAL) NO

   FROM #Test

  ),

Rec (GRP, VAL, NO, NEW_GRP)

 AS

  (

  --(1).Anchor

  SELECT GRP, VAL, NO, GRP NEW_GRP

  FROM Org

  WHERE 1=1

        AND NO = 1

  UNION ALL

  --(2).Recursive

  SELECT C.GRP, C.VAL, C.NO

       , CASE WHEN P.GRP = C.GRP THEN

                   P.NEW_GRP

              ELSE

                   CASE WHEN C.VAL = P.VAL THEN

                             P.NEW_GRP

                        ELSE

                             C.GRP

                   END

         END                 

  FROM Rec P , Org C           

  WHERE 1=1

       AND P.NO = C.NO -1 

  )

SELECT *

FROM Rec

資料

CREATE TABLE #Test

(

GRP VARCHAR(10),

VAL NUMERIC(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 ('BBBB', 4);

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);