Oracle递归和group,Oracle中的递归迭代

我有一张这样的桌子:

+----+-----+------+

| id | ord | test |

+----+-----+------+

| 1 | 1 | A |

| 1 | 2 | B |

| 1 | 3 | C |

| 2 | 1 | B |

| 2 | 2 | C |

+----+-----+------+

(这是一些创建数据的代码)

drop table temp_test;

create table temp_test (id varchar(20), ord varchar(20), test varchar(20));

insert into temp_test (id,ord,test) values ('1','1','A');

insert into temp_test (id,ord,test) values ('1','2','B');

insert into temp_test (id,ord,test) values ('1','3','C');

insert into temp_test (id,ord,test) values ('2','1','B');

insert into temp_test (id,ord,test) values ('2','2','C');

commit;

我怎样才能得到下面的结果?

+----+-----+-------+

| id | ord | test |

+----+-----+-------+

| 1 | 1 | A |

| 1 | 2 | A_B |

| 1 | 3 | A_B_C |

| 2 | 1 | B |

| 2 | 2 | B_C |

+----+-----+-------+

我试过使用lag(),比如:

select CONCAT(lag(TEST) over (partition by ID order by ord),TEST) AS TEST from temp_test;

但它不能递归地工作。

此代码有效:

SELECT

R1.*,

( SELECT LISTAGG(test, ';') WITHIN GROUP (ORDER BY ord)

FROM temp_test R2

WHERE R1.ord >= R2.ord

AND R1.ID = R2.ID

GROUP BY ID

) AS WTR_KEYWORD_1

FROM temp_test R1

ORDER BY id, ord;

但它的性能还不足以容纳更大的数据集。