Oracle SELECT语句中的START WITH和CONNECT BY子句自动阻断一个层次。缺少这项特性，就需要一个复杂的自联接来确定行之间的逻辑联系。START WITH子句指定被认为是层次起点，或“根”的一行或几行。然后CONNECT BY PRIOR子句指明哪些行彼此关联。

例如，列表A中的查询从Oracle HR样本模式的EMPLOYEES表中生成一个“Reports To”列表。

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前后的输出结果。

Bob Watkins(OCP、MCDBA、MCSE、MCT)是一位有25年经验的计算机专业人士，从事过技术培训师、顾问与数据库管理员等职。

责任编辑：德东

查看本文国际来源

column "Reports To" format a30

set pagesize 9999



SELECT LPAD(' ', 2*(LEVEL-1))||last_name "Reports To", employee_id

FROM employees

START WITH employee_id IN (101, 102)

CONNECT BY PRIOR employee_id = manager_id

/

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>