Oracle 如何提取关联记录的不同组 (本质上不完全是分层的)

大家好，

如果有人可以提供一种方法来提取相关的 (它们不是父子关系或层次结构的) 记录的不同组/数组，如下所示，将不胜感激。使用示例数据集创建了相同的表，您将在下面的LiveSQL链接中找到-

https://livesql.oracle.com/apex/livesql/s/hodwakb4rv2siftyzv1juvc4i

CREATE TABLE TEST_DATA
(
SRC_REC NUMBER,
TGT_REC NUMBER
);

INSERT INTO TEST_DATA (SRC_REC, TGT_REC) VALUES(100, 101);
INSERT INTO TEST_DATA (SRC_REC, TGT_REC) VALUES(100, 102);
INSERT INTO TEST_DATA (SRC_REC, TGT_REC) VALUES(103, 102);
INSERT INTO TEST_DATA (SRC_REC, TGT_REC) VALUES(103, 104);
INSERT INTO TEST_DATA (SRC_REC, TGT_REC) VALUES(102, 105);
INSERT INTO TEST_DATA (SRC_REC, TGT_REC) VALUES(108, 110);
INSERT INTO TEST_DATA (SRC_REC, TGT_REC) VALUES(107, 106);
INSERT INTO TEST_DATA (SRC_REC, TGT_REC) VALUES(109, 111);
INSERT INTO TEST_DATA (SRC_REC, TGT_REC) VALUES(111, 107);
COMMIT;

数组1: 100，101，102，103，104，105
数组2: 106，107，111
数组3: 108，110

这是图遍历问题的一种形式。这在SQL中很棘手。

但是你可以使用连接。或递归与如果你喜欢。

对于每一行，递归地将其连接到每隔一行，以下任何一行都是正确的:

-源 = 目标 (反之亦然)
-源 = 源，他们有不同的目标
-目标 = 目标，它们有不同的来源

包括源的根行。这将循环回自己，所以你需要添加周期检测。

select src_rec, tgt_rec, 
       connect_by_root src_rec rt
from   test_data
connect by nocycle
  prior src_rec = tgt_rec or
  prior tgt_rec = src_rec or
  ( prior src_rec = src_rec and prior tgt_rec <> tgt_rec ) or
  ( prior tgt_rec = tgt_rec and prior src_rec <> src_rec );

SRC_REC   TGT_REC   RT    
      100       101   100 
      100       102   100 
      103       102   100 
      103       104   100 
      102       105   100 
      102       105   100 
      103       102   100 
      103       104   100 
      100       102   100 
      100       101   100 
      103       102   100 
      103       104   100 
      102       105   100 
      102       105   100 
      103       102   100 
      103       104   100
... 

这将生成所有行的子图。要将其转换为列表:

-展开源和目标列
-返回结果的不同根和值
-使用listagg获取每个根子图的逗号分隔列表
-返回这些csv的不同列表

这给出了:

with tree ( src, tgt, rt ) as (
  select src_rec, tgt_rec, 
         connect_by_root src_rec rt
  from   test_data
  connect by nocycle
    prior src_rec = tgt_rec or
    prior tgt_rec = src_rec or
    ( prior src_rec = src_rec and prior tgt_rec <> tgt_rec ) or
    ( prior tgt_rec = tgt_rec and prior src_rec <> src_rec )
), vals as ( 
  select distinct rt, val
  from   tree
  unpivot ( val for col in ( src, tgt ) )
)
  select distinct listagg ( val , ',' ) 
            within group ( order by val ) grp
  from   vals
  group  by rt;

GRP                       
106,107,109,111           
108,110                   
100,101,102,103,104,105   

警告: 您正在为每一行生成树。因此，在大型数据集上，这可能需要一段时间!