1、第一种方法
SQL> SELECT TO_NUMBER(SUBSTR(PATH, 2, INSTR(PATH, '/', 2) - 2)) ROOT_ID, ID
2 FROM
3 (
4 SELECT SYS_CONNECT_BY_PATH(ID, '/') || '/' PATH, ID
5 FROM TEST
6 WHERE ROWNUM < 10
7 CONNECT BY PRIOR ID = FID
8 )
9 ;
ROOT_ID ID ------------ ------------
1 1
1 2
1 200516
1 2005859
1 2005862
1 2005864
1 2005866
1 2005867
1 2005865
已选择9行。
2、第二种方法