一张表中,ID的循环查询

Question

TABLE A
===================================================
ID       NAME      UP_ID
----------------------------
11       张三       0
22       李四      11
33       王五      11
44       赵六      33
55       胡七      22
66       李八      22
77       大一      33
88       大二      66
99       大三      11想要的结果是:
属于李四的人有哪些?
胡七,李八,大二(因为大二属于李八,所以属于李四)
请高手指点迷津

Answer 1

-- CONNECT BY专门用于父子、树型查询：
SQL> SELECT * FROM TEST_TAB;        ID NAME      UP_ID
---------- ---- ----------
        11 张三          0
        22 李四         11
        33 王五         11
        44 赵六         33
        55 胡七         22
        66 李八         22
        77 大一         33
        88 大二         66
        99 大三         119 rows selectedExecuted in 0.094 secondsSQL> SELECT TT.*,
  2         SYS_CONNECT_BY_PATH(NAME,',') PATH,
  3         LEVEL LEV
  4    FROM TEST_TAB TT
  5   START WITH NAME = '李四'
  6  CONNECT BY PRIOR ID = UP_ID;        ID NAME      UP_ID PATH                                                                                    LEV
---------- ---- ---------- -------------------------------------------------------------------------------- ----------
        22 李四         11 ,李四                                                                                     1
        55 胡七         22 ,李四,胡七                                                                                2
        66 李八         22 ,李四,李八                                                                                2
        88 大二         66 ,李四,李八,大二                                                                           3Executed in 0.078 seconds

Answer 2

select * from tablea
connect by prior id=up_id
start with name='李四'

Answer 3

SQL> select *
  2  from
  3  (select name from test start with name='李四' connect by prior id=up_id)
  4  where name <>'李四';
 
NAME
--------------------
胡七
李八
大二