如何从联结表中为任何给定 ID 选择所有可能的不同相关"two-ways"值？改 性

Question

我的问题是如何添加与I-300相关的值I-95以及与CCD_ 4相关的CCD_

样本数据为：

CREATE TABLE TBL_IORELATE (ID, MainID, RelatedID) AS
SELECT 1, 'I-225', 'I-20' FROM DUAL UNION ALL
SELECT 2, 'I-225', 'I-35' FROM DUAL UNION ALL
SELECT 3, 'I-225', 'I-300' FROM DUAL UNION ALL
SELECT 4, 'I-410', 'I-20' FROM DUAL UNION ALL
SELECT 5, 'I-410', 'I-50' FROM DUAL UNION ALL
SELECT 6, 'I-300', 'I-95' FROM DUAL UNION ALL
SELECT 7, 'I-455', 'I-300' FROM DUAL UNION ALL
SELECT 8, 'I-20', 'I-390' FROM DUAL;

我想从之前的问题中调整的问题是：

SELECT id,
LISTAGG(value, ',') WITHIN GROUP (ORDER BY value) AS io_related
FROM   (
SELECT DISTINCT id, value
FROM   (
SELECT CONNECT_BY_ROOT MainID AS id,
MainID,
RelatedID
FROM   TBL_IORELATE
START WITH MainID  IN ('I-225')
CONNECT BY NOCYCLE
PRIOR MainID = MainID
OR PRIOR RelatedID = RelatedID
)
UNPIVOT(value FOR key IN (MainID, RelatedID))
WHERE id <> value
)
GROUP BY id

db<gt；在这里摆弄

Answer 1

连接到任何mainid到自身或relatedid，反之亦然：

SELECT id,
LISTAGG(value, ',') WITHIN GROUP (ORDER BY value) AS io_related
FROM   (
SELECT DISTINCT id, value
FROM   (
SELECT CONNECT_BY_ROOT MainID AS id,
MainID,
RelatedID
FROM   TBL_IORELATE
START WITH MainID  IN ('I-225')
CONNECT BY NOCYCLE
PRIOR MainID IN (RelatedID, MainID)
OR PRIOR RelatedID IN (RelatedID, MainID)
)
UNPIVOT(value FOR key IN (MainID, RelatedID))
WHERE id <> value
)
GROUP BY id

哪个输出：

ID	IO_RELATED
I-225	I-20、I-300、I-35、I-390、I-410、I-455、I-50、I-95