在这个例子里面,关于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;
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;