当前位置:操作系统 > Unix/Linux >>

[每日一题]OCP1z0-047 :2013-07-23 CONNECT BY PRIOR层次查询

[每日一题]OCP1z0-047 :2013-07-23 CONNECT BY PRIOR层次查询
 
 
你想获取员工表中使用自顶向下的层次结构分层数据,那很明显就是答案就是:E
 
Finally, the next example adds aSTART WITH clause to specify a root row for the hierarchy, and an ORDER BYclause using the SIBLINGS keyword to preserve ordering within the hierarchy:
 
 
hr@OCM> SELECT last_name,employee_id, manager_id, LEVEL
 2        FROM employees
 3        START WITH employee_id =100
 4        CONNECT BY PRIORemployee_id = manager_id
 5        ORDER SIBLINGS BYlast_name;

LAST_NAME                 EMPLOYEE_ID MANAGER_ID      LEVEL
------------------------------------ ---------- ----------
King                              100                     1
Cambrault                         148        100          2
Bates                             172        148          3
Bloom                             169        148          3
Fox                               170        148          3
Kumar                             173        148          3
Ozer                              168        148          3
Smith                             171       148          3
De Haan                           102        100          2
Hunold                            103        102          3
Austin                            105        103          4
Ernst                             104        103          4
Lorentz                           107        103          4
Pataballa                         106        103          4
Errazuriz                         147        100          2
Ande                              166        147          3
Banda                             167        147          3
Greene                            163        147          3
Lee                               165        147          3
Marvins                           164        147          3
Vishney                           162        147          3
Fripp                             121        100          2
Atkinson                          130        121          3
Bissot                            129        121          3
Bull                              185        121          3
Cabrio                            187        121          3
Dellinger                         186        121          3
Marlow                            131        121          3
Olson                             132        121          3
Sarchand                          184        121          3
Hartstein                         201        100          2
Fay                               202        201          3
Kaufling                          122        100          2
Chung                             188        122          3
Dilly                             189        122          3
Gates                             190        122          3
Gee                               135       122          3
Mallin                            133        122          3
Perkins                           191        122          3
Philtanker                        136        122          3
Rogers                            134        122          3
Kochhar                           101        100          2
Baer                              204        101          3
Greenberg                         108        101          3
Chen                              110        108          4
Faviet                            109        108          4
Popp                              113        108          4
Sciarra                           111        108          4
Urman                             112        108          4
Higgins                           205        101          3
Gietz                             206        205          4
Mavris                            203        101          3
Whalen                            200        101          3
Mourgos                           124        100          2
Davies                            142        124          3
Feeney                            197        124          3
Grant                             199        124          3
Matos                             143        124          3
OConnell                          198        124          3
Rajs                              141        124          3
Vargas                            144        124          3
Walsh                             196        124          3
Partners                          146        100          2
Doran                             160        146          3
King                              156        146          3
McEwen                            158       146          3
Sewall                            161        146          3
Smith                             159        146          3
Sully                             157        146          3
Raphaely                          114        100          2
Baida                             116        114          3
Colmenares                        119        114          3
Himuro                            118        114          3
Khoo                              115        114          3
Tobias                            117        114          3
Russell                           145        100          2
Bernstein                         151        145          3
Cambrault                         154        145          3
Hall                              152        145          3
Olsen                             153        145          3
Tucker                            150        145          3
Tuvault                           155        145          3
Vollman                           123        100          2
Bell                              192        123          3
Everett                           193        123          3
Jones                             195        123          3
Ladwig                            137        123          3
McCain                            194        123          3
Patel                             140        123          3
Seo                               139        123          3
Stiles                            138        123          3
Weiss                             120        100          2
Fleaur                            181        120          3
Geoni                             183        120          3
Landry                            127       120          3
Markle                            128        120          3
Mikkilineni                       126        120          3
Nayer                             125        120          3
Sullivan                          182        120          3
Taylor                            180        120          3
Zlotkey                           149        100          2
Abel                              174        149          3
Grant                             178        149          3
Hutton                            175        149          3
Johnson                           179        149          3
Livingston                        177        149          3
Taylor                            176        149          3

 

 
Oracle中start with...connect by prior子句用法
connect by 是结构化查询中用到的,其基本语法是:
select ... from tablename start with 条件1
connect by 条件2
where 条件3;
例:
select * from table
start with org_id = 'HBHqfWGWPy'
connect by prior org_id = parent_id;

 

简单说来是将一个树状结构存储在一张表里,比如一个表中存在两个字段:
org_id,parent_id那么通过表示每一条记录的parent是谁,就可以形成一个树状结构。
用上述语法的查询可以取得这棵树的所有记录。
其中:
条件1是根结点的限定语句,当然可以放宽限定条件,以取
CopyRight © 2012 站长网 编程知识问答 www.zzzyk.com All Rights Reserved
部份技术文章来自网络,