Oracle Start with...Connect By子句递归查询

 

Start with...Connect By子句递归查询一般用于一个表维护树形结构的应用。

SQL> Select * From test2 ;
        ID NAME            MANID
---------- ---------- ----------
         1 Pataballa           0
         2 Lorentz             1
         3 Diana               2
         4 Valli               3
         5 AALIYAH             1
         6 Diminutiv           2
         7 AMIAS               2
         8 ASTOR               3
         9 derived f           4
        10 derive              4
10 rows selected

id是人员id，manid是领导id，我们想要得到一个由上到下的遍历

SQL> Select * From
  2  test2
  3  Start With Id=1
  4  Connect By Prior Id=manid Order By manid
  5  /
        ID NAME            MANID
---------- ---------- ----------
         1 Pataballa           0
         2 Lorentz             1
         5 AALIYAH             1
         7 AMIAS               2
         3 Diana               2
         6 Diminutiv           2
         4 Valli               3
         8 ASTOR               3
        10 derive              4
         9 derived f           4
10 rows selected
SQL>

SQL> Select level,a.* From
  2  test2 a
  3  Start With Id=1
  4  Connect By Prior Id=manid Order By manid
  5  /
     LEVEL         ID NAME            MANID
---------- ---------- ---------- ----------
         1          1 Pataballa           0
         2          2 Lorentz             1
         2          5 AALIYAH             1
         3          7 AMIAS               2
         3          3 Diana               2
         3          6 Diminutiv           2
         4          4 Valli               3
         4          8 ASTOR               3
         5         10 derive              4
         5          9 derived f           4
10 rows selected
SQL>