SQL Oracle-获得有路径之间的ID组

Question

我有这样的表：

ID1 | ID2
==========
A   | B
C   | B
A   | F
G   | B
G   | E
J   | B
X   | Y
Z   | Y

我必须编写SQL选择该发现（标记）单独的组。在此示例中，我有两个组：A，B，C，F，G，E，J和X，Y，Z。就像所有之间有路径（无方向）（例如家庭关系）一样。无论是 listagg >> 连接 例如），例如）或带有组标识符的新列，例如

ID1 | ID2 | GROUP
=================
A   | B   | 1
C   | B   | 1
A   | F   | 1
G   | B   | 1
G   | E   | 1
J   | B   | 1
X   | Y   | 2
Z   | Y   | 2

如果[A，z]还有另外一排，则所有不同的字母都是一组。我认为它必须使用>或 cycle 进行连接，但我不知道它看起来如何。

Answer 1

根据我的答案改编：

sql小提琴

Oracle 11G R2架构设置：

CREATE TABLE ids ( ID1, ID2 ) AS
  SELECT 'A', 'B' FROM DUAL UNION ALL
  SELECT 'C', 'B' FROM DUAL UNION ALL
  SELECT 'A', 'F' FROM DUAL UNION ALL
  SELECT 'G', 'B' FROM DUAL UNION ALL
  SELECT 'G', 'E' FROM DUAL UNION ALL
  SELECT 'J', 'B' FROM DUAL UNION ALL
  SELECT 'X', 'Y' FROM DUAL UNION ALL
  SELECT 'Z', 'Y' FROM DUAL;

查询1 ：

WITH indexed_ids ( id, id1, id2 ) AS (
  SELECT ROWNUM, id1, id2 FROM ids
),
grouped_ids ( id, id1, id2, min_id ) AS (
  SELECT i.*,
         LEAST(
           MIN( id ) OVER ( PARTITION BY id1 ),
           MIN( id ) OVER ( PARTITION BY id2 )
         ) AS min_id
  FROM   indexed_ids i
)
SELECT id, id1, id2,
       MIN( "GROUP" ) AS "GROUP"
FROM   (
  SELECT id, id1, id2,
         CONNECT_BY_ROOT( id ) AS "GROUP"
  FROM   grouped_ids g
  START WITH id = min_id
  CONNECT BY NOCYCLE ( PRIOR id1 IN ( id1, id2 ) OR PRIOR id2 IN ( id1, id2 ) )
)
GROUP BY id, id1, id2
ORDER BY id

结果：

| ID | ID1 | ID2 | GROUP |
|----|-----|-----|-------|
|  1 |   A |   B |     1 |
|  2 |   C |   B |     1 |
|  3 |   A |   F |     1 |
|  4 |   G |   B |     1 |
|  5 |   G |   E |     1 |
|  6 |   J |   B |     1 |
|  7 |   X |   Y |     7 |
|  8 |   Z |   Y |     7 |

GROUP列标识组的最小ID。如果您想要一个顺序的GROUP，则可以使用DENSE_RANK()分析功能。

Answer 2

我认为您可以在这里使用connect_by_root：

select id1, listagg(root) within group (order by root) list
  from (select distinct id1, root
          from (select t.*, connect_by_root(id1) root
                  from (select id1, id2 from t union select id2, id1 from t) t
                  connect by nocycle prior id1 = id2))
  group by id1

...给我们：

A   ABCEFGJ
B   ABCEFGJ
C   ABCEFGJ
E   ABCEFGJ
F   ABCEFGJ
G   ABCEFGJ
J   ABCEFGJ
X   XYZ
Y   XYZ
Z   XYZ

休息很简单，使用dense_rank()并与原始表一起加入：

with t(id1, id2) as (
    select 'A', 'B' from dual union all
    select 'C', 'B' from dual union all
    select 'A', 'F' from dual union all
    select 'G', 'B' from dual union all
    select 'G', 'E' from dual union all
    select 'J', 'B' from dual union all
    select 'X', 'Y' from dual union all
    select 'Z', 'Y' from dual),
q as (
    select id1, listagg(root) within group (order by root) list
      from (select distinct id1, root
              from (select t.*, connect_by_root(id1) root
                      from (select id1, id2 from t union select id2, id1 from t) t
                      connect by nocycle prior id1 = id2))
      group by id1)
select id1, id2, dense_rank() over (order by list) grp
  from t join q using (id1)

结果：

ID1 ID2        GRP
--- --- ----------
A   F            1
A   B            1
C   B            1
G   E            1
J   B            1
G   B            1
X   Y            2
Z   Y            2
8 rows selected