Oracle找团伙的写法。

现有表test_a,有字段col_a,col_b，其值如下
a1    a4
a1    a3
a4    a2
a3    a6
a4    a1
a4    a8
a10   a5
a5    a9

需要得到结果
a1,a3,a4,a2,a6,a8为一集合
a5,a9,a10为一集合

现在我们用pl/sql来实现。

SQL> CREATE TABLE TEST AS
  2   select 'a1' l,'a4' r from dual union all
  3   select 'a1','a3' from dual union all
  4   select 'a4','a2' from dual union all
  5   select 'a3','a6' from dual union all
  6   select 'a4','a1' from dual union all
  7   select 'a4','a8' from dual union all
  8   select 'a10','a5' from dual union all
  9   select 'a5','a9' from dual
 10   ;
 
Table created
SQL>  CREATE TABLE t_res (node VARCHAR2(10),lvl NUMBER,group_id NUMBER);
 
Table created

SQL>  DECLARE
  2     l_level NUMBER;
  3     l_group_id NUMBER :=1;
  4   BEGIN
  5  
  6      LOOP
  7         l_level:=1;
  8  
  9         INSERT INTO t_res
 10         WITH v AS (
 11            SELECT l,r
 12              FROM test
 13             WHERE NOT EXISTS (SELECT 1 FROM t_res WHERE t_res.node IN (l,r))
 14                   AND ROWNUM=1
 15         )
 16         SELECT l,l_level,l_group_id
 17           FROM v
 18         UNION
 19         SELECT r,l_level,l_group_id
 20           FROM v;
 21  
 22         EXIT WHEN SQL%ROWCOUNT=0;
 23  
 24         LOOP
 25            MERGE INTO t_res
 26            USING (SELECT DISTINCT (CASE WHEN node=l THEN r ELSE l END) node
 27                     FROM test,(SELECT node FROM t_res WHERE lvl=l_level AND group_id=l_group_id)
 28                    WHERE node IN (test.l,test.r)
 29                   ) n
 30            ON (t_res.node=n.node)
 31            WHEN NOT MATCHED THEN INSERT VALUES (n.node,l_level+1,l_group_id);
 32  
 33            EXIT WHEN SQL%ROWCOUNT=0;
 34            l_level := l_level+1;
 35         END LOOP;
 36         l_group_id := l_group_id+1;
 37     END LOOP;
 38   END;
 39   /

SQL> select * from t_res;
 
NODE              LVL   GROUP_ID
---------- ---------- ----------
a1                  1          1
a4                  1          1
a2                  2          1
a3                  2          1
a8                  2          1
a6                  3          1
a10                 1          2
a5                  1          2
a9                  2          2
 
9 rows selected
 
SQL> 
SQL>  select listagg(node,',') within group(order by node) As sz from t_res group by group_id;
 
SZ
--------------------------------------------------------------------------------
a1,a2,a3,a4,a6,a8
a10,a5,a9
 
SQL>

用sql语句来实现

WITH TEMP AS
 (
 SELECT 'a1'  COL_A,'a4' COL_B FROM DUAL UNION
 SELECT 'a1'  COL_A,'a3' COL_B FROM DUAL UNION
 SELECT 'a4'  COL_A,'a2' COL_B FROM DUAL UNION
 SELECT 'a3'  COL_A,'a6' COL_B FROM DUAL UNION
 SELECT 'a4'  COL_A,'a1' COL_B FROM DUAL UNION
 SELECT 'a4'  COL_A,'a8' COL_B FROM DUAL UNION
 SELECT 'a10' COL_A,'a5' COL_B FROM DUAL UNION
 SELECT 'a5'  COL_A,'a9' COL_B FROM DUAL
 )
 SELECT WMSYS.WM_CONCAT(TEXT) FROM
   (SELECT TEXT,RT,ROW_NUMBER() OVER(PARTITION BY TEXT ORDER BY RN1) RN2 FROM
     (SELECT COL_B TEXT,ROWNUM RN1,CONNECT_BY_ROOT(COL_A) RT --- 这个ROOT的作用和原来的GROP其实是一个道理
       FROM
       (SELECT * FROM TEMP
        UNION SELECT COL_B,COL_A FROM TEMP  --- 加上双向遍历
      ) CONNECT BY NOCYCLE COL_A = PRIOR COL_B
     ) T 
   ) T WHERE RN2 = 1
 GROUP BY RT;
这个方法依赖于CONNECT BY的输出顺序。

SELECT DISTINCT MAX(res)
   FROM (select l, wmsys.wm_concat(r) OVER(PARTITION BY l ORDER BY r) res  ---GROUP BY中的wm_concat不能排序所以先用分析函数。如果用11GR2的LISTAGG的within group (ORDER BY...)则可以省略这一步
          from (select distinct connect_by_root l l, r
                   from (select l, r
                           from test
                         union
                         select r, l from test)
                 connect by nocycle l = prior r
                 )
        )
 GROUP BY l;

 CONNECT BY的方法有不必要的遍历，PLSQL的方法必须反复扫描表，各有优缺点，但数据量大了都吃不消！

SQL>