你不想要一个“循环”。您希望使用分层查询并查找每个根的分层树的叶子：

SELECT id,
       CONNECT_BY_ROOT i1 AS i1,
       i2
FROM   table_name
WHERE  CONNECT_BY_ISLEAF = 1
CONNECT BY
       PRIOR i2 = i1
AND    PRIOR id = id -- Not sure if this is needed as you only have one id value

字符串
其中，对于样本数据：

CREATE TABLE table_name (id, i1, i2) AS
SELECT 'Y001', 'x1', 'a1' FROM DUAL UNION ALL
SELECT 'Y001', 'x1', 'a2' FROM DUAL UNION ALL
SELECT 'Y001', 'a1', 'a5' FROM DUAL UNION ALL
SELECT 'Y001', 'a2', 'a3' FROM DUAL UNION ALL
SELECT 'Y001', 'a3', 'a4' FROM DUAL;

型
输出：
| I1| I2| I2 |
| --|--| ------------ |
| a1| A5| a5 |
| A2| A4| a4 |
| A3| A4| a4 |
| x1| A5| a5 |
| x1| A4| a4 |
fiddle