如何在Oracle層次查詢中給SIBLINGS排序

Oracle SELECT語句中的START WITH和CONNECT BY子句自動阻斷一個層次。缺少這項特性，就需要一個復雜的自聯接來確定行之間的邏輯聯系。START WITH子句指定被認為是層次起點，或“根”的一行或幾行。然后CONNECT BY PRIOR子句指明哪些行彼此關聯。

例如，列表A中的查詢從Oracle HR樣本模式的EMPLOYEES表中生成一個“Reports To”列表。

column 'Reports To' format a30set pagesize 9999SELECT LPAD(' ', 2*(LEVEL-1))||last_name 'Reports To', employee_idFROM employeesSTART WITH employee_id IN (101, 102)CONNECT BY PRIOR employee_id = manager_id/

列表A

LEVEL偽列表明報告當前嵌套的深度，這里我使用LPAD雇員姓名對它們進行縮排。START WITH條件指出只有雇員101和102被認為是起點。然后CONNECT BY PRIOR子句將一行中的employee_id列與另一行的manager_id列連接起來，指出誰向誰報告。

如果你在HR模式中運行這個查詢，你會注意到某個經理列表中的姓沒有分類，它們以Oracle在處理層次時遇到它們的順序排列。

如果你希望下屬以字母順序排列，你可以嘗試對原始的last_name列使用ORDER BY。但是，這樣會破壞層次，把它變回一個單調的姓名列表。

你還可以首先對偽列LEVEL使用ORDER BY，它說明某個特殊行在層次中的深度。這同樣也會破壞層次，首先會列出所有的經理，然后是向他們報告的雇員。

在Oracle 10g（兩個版本）中，現在很容易實現這一點：你可以使用新的SIBLINGS關鍵字建立正確的順序。其語法如下：

ORDER SIBLINGS BY <expression>

因此在查詢結尾處增加下面這個子句：

ORDER SIBLINGS BY last_name

將會保護層次，并在每個等級中以字母順序排列雇員的姓。注意最初的last_name用作“Reports To”的別名。“Reports To”中的額外空間會影響排序，因此必須使用最初的last_name。列表B中是增加ORDER SIBLINGS BY前后的輸出結果。

SQL> @siblings_without_orderbyReports To EMPLOYEE_ID------------------------------ -----------Kochhar101Whalen 200Mavris 203Baer 204Higgins205Gietz206Greenberg 108Faviet 109Chen 110Sciarra111Urman112Popp 113De Haan102Hunold 103Ernst104Austin 105Pataballa 106Lorentz10718 rows selected.SQL> @siblings_with_orderbyReports To EMPLOYEE_ID------------------------------ -----------De Haan102Hunold 103Austin 105Ernst104Lorentz107Pataballa 106Kochhar101Baer 204Greenberg 108Chen 110Faviet 109Popp 113Sciarra111Urman112Higgins205Gietz206Mavris 203Whalen 20018 rows selected.SQL>

列表B