求一SQL算法【找团伙】

water1108

现有表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为一集合

意思就是找出col_a与其相联系的其它列作为一个集合，集合之间没有交集

fenglixiang

想用connect by做，好象不行。期待高手

〇〇

不太容易
我只能做到
with tmp as(
select 1 rn,'a1'l,'a4'r from dual union all
select 2,'a1','a3' from dual union all
select 3,'a4','a2' from dual union all
select 4,'a3','a6' from dual union all
select 5,'a4','a1' from dual union all
select 6,'a4','a8' from dual union all
select 7,'a10','a5' from dual union all
select 8,'a5','a9' from dual
),
t1 as(
select max(sys_connect_by_path(l,' '))s,l
from (select r,l from tmp union select l,r from tmp)
connect by nocycle prior l=r
group by l
order by 1
)
select s,l from t1;
SQL> with tmp as(
  2  select 1 rn,'a1'l,'a4'r from dual union all
  3  select 2,'a1','a3' from dual union all
  4  select 3,'a4','a2' from dual union all
  5  select 4,'a3','a6' from dual union all
  6  select 5,'a4','a1' from dual union all
  7  select 6,'a4','a8' from dual union all
  8  select 7,'a10','a5' from dual union all
  9  select 8,'a5','a9' from dual
10  ),
11  t1 as(
12  select max(sys_connect_by_path(l,' '))s,l
13  from (select r,l from tmp union select l,r from tmp)
14  connect by nocycle prior l=r
15  group by l
16  order by 1
17  )
18  select s,l from t1;

S                              L
------------------------------ ---
a8                            a8
a8 a4                         a4
a8 a4 a1                      a1
a8 a4 a1 a3                   a3
a8 a4 a1 a3 a6                a6
a8 a4 a2                      a2
a9                            a9
a9 a5                         a5
a9 a5 a10                     a10

9 rows selected.

〇〇

然后对2的结果找出不是其他行的子串
a8 a4 a1 a3 a6                a6
a8 a4 a2                      a2
a9 a5 a10                     a10

再想办法把1，2行合并，因为它们有公共子串

newkid

只好用PLSQL了：
CREATE TABLE TEST AS
select 'a1' l,'a4' r from dual union all
select 'a1','a3' from dual union all
select 'a4','a2' from dual union all
select 'a3','a6' from dual union all
select 'a4','a1' from dual union all
select 'a4','a8' from dual union all
select 'a10','a5' from dual union all
select 'a5','a9' from dual
;


CREATE TABLE t_res (node VARCHAR2(10),lvl NUMBER,group_id NUMBER);

DECLARE
  l_level NUMBER;
  l_group_id NUMBER :=1;
BEGIN
   
   LOOP
      l_level:=1;
      
      INSERT INTO t_res
      WITH v AS (
         SELECT l,r
           FROM test
          WHERE NOT EXISTS (SELECT 1 FROM t_res WHERE t_res.node IN (l,r))
                AND ROWNUM=1
      )
      SELECT l,l_level,l_group_id
        FROM v
      UNION
      SELECT r,l_level,l_group_id
        FROM v;
      
      EXIT WHEN SQL%ROWCOUNT=0;

      LOOP
         MERGE INTO t_res
         USING (SELECT DISTINCT (CASE WHEN node=l THEN r ELSE l END) node
                  FROM test,(SELECT node FROM t_res WHERE lvl=l_level AND group_id=l_group_id)
                 WHERE node IN (test.l,test.r)
                ) n
         ON (t_res.node=n.node)
         WHEN NOT MATCHED THEN INSERT VALUES (n.node,l_level+1,l_group_id);
               
         EXIT WHEN SQL%ROWCOUNT=0;
         l_level := l_level+1;
      END LOOP;
      l_group_id := l_group_id+1;
  END LOOP;
END;
/

select * from t_res ORDER BY group_id,lvl;

NODE              LVL   GROUP_ID
---------- ---------- ----------
a4                  1          1
a1                  1          1
a3                  2          1
a2                  2          1
a8                  2          1
a6                  3          1
a10                 1          2
a5                  1          2
a9                  2          2

〇〇

记录学习

water1108

感谢你的思想和辛苦，小弟万分感激。对你的算法真是受易非浅。

chen222246lei

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 MAX(TEXT1) TEXT FROM
(SELECT WMSYS.WM_CONCAT(TEXT) OVER(PARTITION BY GROP ORDER BY RN1) TEXT1,T.* FROM
  (SELECT TEXT,RN1,SUM(GROP) OVER(ORDER BY RN1) GROP,ROW_NUMBER() OVER(PARTITION BY TEXT ORDER BY RN1) RN2 FROM
    (SELECT COL_B TEXT,ROWNUM RN1,DECODE(LEVEL,1,1,0) GROP FROM
      (SELECT NULL COL_A,COL_A COL_B FROM TEMP GROUP BY COL_A
       UNION
       SELECT * FROM TEMP
      ) CONNECT BY NOCYCLE COL_A = PRIOR COL_B START WITH COL_A IS NULL
    ) T ORDER BY RN1
  ) T WHERE RN2 = 1
  ) GROUP BY GROP

1        a1,a3,a6,a4,a2,a8
2        a10,a5,a9

sxlcom

mark,佩服

〇〇

connect和分析函数结合,好,改写一下,不使用WMSYS.WM_CONCAT
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 MAX(TEXT1) TEXT FROM
--(SELECT WMSYS.WM_CONCAT(TEXT) OVER(PARTITION BY GROP ORDER BY RN1) TEXT1,T.* FROM
select  max(sys_connect_by_path(text,',')) from
  (select * from
  (SELECT TEXT,RN1,SUM(GROP) OVER(ORDER BY RN1) GROP,ROW_NUMBER() OVER(PARTITION BY TEXT ORDER BY RN1) RN2 FROM
    (SELECT COL_B TEXT,ROWNUM RN1,DECODE(LEVEL,1,1,0) GROP FROM
      (SELECT NULL COL_A,COL_A COL_B FROM TEMP GROUP BY COL_A
       UNION
       SELECT * FROM TEMP
      ) CONNECT BY NOCYCLE COL_A = PRIOR COL_B START WITH COL_A IS NULL
    ) T ORDER BY RN1
  ) T WHERE RN2 = 1)
  connect by prior rn1=rn1+1 and prior grop=grop
  GROUP BY GROP

[ 本帖最后由 〇〇 于 2009-11-4 15:33 编辑 ]