这个条件的查询语句怎么写吖？ 难道要用循环？（续）

表A 中，有ID，父ID，名称，三个字段。
 如何通过id获取某ID的父ID记录（父ID还有父ID）并按ID排序。 父ID 其实就是指向ID。 如何获取所有？ 父ID 为0 代表无。 例：

表A：
id name fatherID
1 aaaaa 0
2 bbbbb 1
3 ccccc 2
4 ddddd 0
5 eeeee 3
6 fffff 5

传入 where id=6 获取后记录为：
id name fatherID
1 aaaaa 0
2 bbbbb 1
3 ccccc 2
5 eeeee 3
6 fffff 5

SQL code

[SYS@myoracle] SQL>WITH A AS(
  2  SELECT 1 ID,'aaaaa' NAME , 0 fatherID FROM DUAL UNION ALL
  3  SELECT 2 ID,'bbbbb' NAME , 1 fatherID FROM DUAL UNION ALL
  4  SELECT 3 ID,'ccccc' NAME , 2 fatherID FROM DUAL UNION ALL
  5  SELECT 4 ID,'ddddd' NAME , 0 fatherID FROM DUAL UNION ALL
  6  SELECT 5 ID,'eeeee' NAME , 3 fatherID FROM DUAL UNION ALL
  7  SELECT 6 ID,'fffff' NAME , 5 fatherID FROM DUAL
  8  )SELECT ID,NAME, fatherID
  9     FROM A
 10    START WITH ID = 6
 11    CONNECT BY PRIOR fatherID = ID
 12   ORDER BY ID
 13  ;

        ID NAME    FATHERID
---------- ----- ----------
         1 aaaaa          0
         2 bbbbb          1
         3 ccccc          2
         5 eeeee          3
         6 fffff          5

1楼 学习了

SQL code

with tbl as
(
    select 1 as id, 'aaaaa' as name, 0 as fatherID from dual
     union all
    select 2 as id, 'bbbbb' as name, 1 as fatherID from dual
     union all
    select 3 as id, 'ccccc' as name, 2 as fatherID from dual
     union all
    select 4 as id, 'ddddd' as name, 0 as fatherID from dual
     union all
    select 5 as id, 'eeeee' as name, 3 as fatherID from dual
     union all
    select 6 as id, 'fffff' as name, 5 as fatherID from dual
)
select id,max(substr(sys_connect_by_path(fatherid,','),2)) 
from tbl
where id='6'
connect by prior id = fatherid
start with fatherid =0
group by id

--result:
6    0,1,2,3,5

都查好主意,多學習connect by ...