[每日一题]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是根结点的限定语句，当然可以放宽限定条件，以取
