11g新特性 递归子查询 替代 Connect by

在这个例子里面，关于history的记录，每条记录都有一个prv_trx_history_id指向上一条的记录， 因此，可以采用递归的方式列出所有的History. (注，此处不用order by creation_date的原因是，可能同一时间可能会插入多条，这样就无法区分先后)


select his.customer_trx_id, his.transaction_history_id, his.prv_trx_history_id, his.status, his.event from ar_transaction_history_all his
where his.customer_trx_id=300100004050089
start with his.prv_trx_history_id is null connect by prior his.transaction_history_id=his.prv_trx_history_id;

--------------

with his(customer_trx_id,transaction_history_id,prv_trx_history_id,status,event)
as (
select customer_trx_id,transaction_history_id,prv_trx_history_id,status,event from ar_transaction_history_all where customer_trx_id=300100004050089 and prv_trx_history_id is null
union all
select t.customer_trx_id,t.transaction_history_id,t.prv_trx_history_id,t.status,t.event from ar_transaction_history_all t, his where t.customer_trx_id=300100004050089 and t.prv_trx_history_id = his.transaction_history_id and t.prv_trx_history_id is not null
)
select * from his;