Oracle SELECT语句中的START WITH和CONNECT BY子句自动阻断一个层次。缺少这项特性,就需要一个复杂的自联接来确定行之间的逻辑联系。
SQL> @siblings_without_orderby
Reports To EMPLOYEE_ID
------------------------------ -----------
Kochhar 101
Whalen 200
Mavris 203
Baer 204
Higgins 205
Gietz 206
Greenberg 108
Faviet 109
Chen 110
Sciarra 111
Urman 112
Popp 113
De Haan 102
Hunold 103
Ernst 104
Austin 105
Pataballa 106
Lorentz 107
18 rows selected.
SQL> @siblings_with_orderby
Reports To EMPLOYEE_ID
------------------------------ -----------
De Haan 102
Hunold 103
Austin 105
Ernst 104
Lorentz 107
Pataballa 106
Kochhar 101
Baer 204
Greenberg 108
Chen 110
Faviet 109
Popp 113
Sciarra 111
Urman 112
Higgins 205
Gietz 206
Mavris 203
Whalen 200
18 rows selected.
SQL>