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>