分析函式connect by | start with學習

/*

某產品加工工序
當前工序，下個工序
A05,A06
A06,A07
A10,A11
A11,A12
A12,A05

用oracle-sql,求工序的順序清單
>>> sort >>
A10
A11
A12
A05
A06
A07

*/

create table test
(
aaa varchar2(10)
,bbb varchar2(10)
)

insert into test values('A05','A06');
insert into test values('A06','A07');
insert into test values('A10','A11');
insert into test values('A11','A12');
insert into test values('A12','A05');
insert into test values('A13','A14');
--insert into test values('A14','A15');
--insert into test values('A15','A01');
--insert into test values('A01','A02');
--insert into test values('A02','A03');
commit;

解決辦法:

select aaa
from test
start with aaa=(
select aaa
from test
where aaa not in(select bbb from test))
connect by prior bbb=aaa
union all
select bbb
from test
where bbb not in(select aaa from test);

[@more@]