我的問題是如何添加與IO_RELATED 輸出I-95 相關I-300
和相關的值I-390I-20
樣本資料為：
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<>在這里擺弄
uj5u.com熱心網友回復：
連接到任何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
db<>在這里擺弄
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/428092.html