1，基本的connect by
select lpad(' ', level * 2 - 1, ' ') || emp.emp_last_name emp_last_name,
emp.emp_first_name,
emp.employee_id,
emp.mgr_last_name,
emp.mgr_first_name,
department_name
from (select e.last_name emp_last_name,
e.first_name emp_first_name,
e.employee_id,
d.department_id,
e.manager_id,
d.department_name,
es.last_name mgr_last_name,
es.first_name mgr_first_name
from scott.employees e
left outer join scott.departments d
on d.department_id = e.department_id
left outer join scott.employees es
on es.employee_id = e.manager_id) emp
connect by prior emp.employee_id = emp.manager_id
start with emp.manager_id is null
order siblings by emp.emp_last_name;Stephen king 的employee_id=100，prior运算符会首先将manager_id是100的数据找到并将它们放在Stephen king层级下，满足条件的是
Cambrault Gerald 、De Haan Lex、Errazuriz Alberto...等，然后依次对Cambrault Gerald 、De Haan Lex、Errazuriz Alberto..这些人重复
上面的处理，先看Cambrault Gerald，employee_id=148，把manager_id是148的数据找到，并将它们放在Cambrault Gerald层级下，
满足条件的有6条数据（Bates Elizabeth、Bloom Harrison、Fox Tayler、Kumar Sundita、Ozer Lisa、Smith William），由于这6名员工
的employee_id值都没有任何manager_id与之匹配，Oracle将会转到还没有进行处理的数据行的层级上（这里是De Haan Lex）并继续
处理，知道所有的数据行都处理完毕。
level伪列保存了递归的深度值，使得可以通过一个简单的方法对输出进行缩进，从而可以直观地看出组织层次结构。
EMP_LAST_NAME EMP_FIRST_NAME TO_NUMBER(EMP.EMPLOYEE_ID) MGR_LAST_NAME MGR_FIRST_NAME DEPARTMENT_NAME
-------------------- -------------------- -------------------------- -------------------- -------------------- --------------------
King Steven 100 Executive
Cambrault Gerald 148 King Steven Sales
Bates Elizabeth 172 Cambrault Gerald Sales
Bloom Harrison 169 Cambrault Gerald Sales
Fox Tayler 170 Cambrault Gerald Sales
Kumar Sundita 173 Cambrault Gerald Sales
Ozer Lisa 168 Cambrault Gerald Sales
Smith William 171 Cambrault Gerald Sales
De Haan Lex 102 King Steven Executive
Hunold Alexander 103 De Haan Lex IT
Austin David 105 Hunold Alexander IT
Ernst Bruce 104 Hunold Alexander IT
Lorentz Diana 107 Hunold Alexander IT
Pataballa Valli 106 Hunold Alexander IT
Errazuriz Alberto 147 King Steven Sales
Ande Sundar 166 Errazuriz Alberto Sales
Banda Amit 167 Errazuriz Alberto Sales
Greene Danielle 163 Errazuriz Alberto Sales
Lee David 165 Errazuriz Alberto Sales
Marvins Mattea 164 Errazuriz Alberto Sales
Vishney Clara 162 Errazuriz Alberto Sales
Fripp Adam 121 King Steven Shipping
Atkinson Mozhe 130 Fripp Adam Shipping
Bissot Laura 129 Fripp Adam Shipping
Bull Alexis 185 Fripp Adam Shipping
Cabrio Anthony 187 Fripp Adam Shipping
Dellinger Julia 186 Fripp Adam Shipping
Marlow James 131 Fripp Adam Shipping
Olson TJ 132 Fripp Adam Shipping
Sarchand Nandita 184 Fripp Adam Shipping
Hartstein Michael 201 King Steven Marketing
Fay Pat 202 Hartstein Michael Marketing
Kaufling Payam 122 King Steven Shipping
Chung Kelly 188 Kaufling Payam Shipping
Dilly Jennifer 189 Kaufling Payam Shipping
Gates Timothy 190 Kaufling Payam Shipping
Gee Ki 135 Kaufling Payam Shipping
Mallin Jason 133 Kaufling Payam Shipping
Perkins Randall 191 Kaufling Payam Shipping
Philtanker Hazel 136 Kaufling Payam Shipping
Rogers Michael 134 Kaufling Payam Shipping
Kochhar Neena 101 King Steven Executive
Baer Hermann 204 Kochhar Neena Public Relations
Greenberg Nancy 108 Kochhar Neena Finance
Chen John 110 Greenberg Nancy Finance
Faviet Daniel 109 Greenberg Nancy Finance
Popp Luis 113 Greenberg Nancy Finance
Sciarra Ismael 111 Greenberg Nancy Finance
Urman Jose Manuel 112 Greenberg Nancy Finance
Higgins Shelley 205 Kochhar Neena Accounting
Gietz William 206 Higgins Shelley Accounting
Mavris Susan 203 Kochhar Neena Human Resources
Whalen Jennifer 200 Kochhar Neena Administration
Mourgos Kevin 124 King Steven Shipping
Davies Curtis 142 Mourgos Kevin Shipping
Feeney Kevin 197 Mourgos Kevin Shipping
Grant Douglas 199 Mourgos Kevin Shipping
Matos Randall 143 Mourgos Kevin Shipping
OConnell Donald 198 Mourgos Kevin Shipping
Rajs Trenna 141 Mourgos Kevin Shipping
Vargas Peter 144 Mourgos Kevin Shipping
Walsh Alana 196 Mourgos Kevin Shipping
Partners Karen 146 King Steven Sales
Doran Louise 160 Partners Karen Sales
King Janette 156 Partners Karen Sales
McEwen Allan 158 Partners Karen Sales
Sewall Sarath 161 Partners Karen Sales
Smith Lindsey 159 Partners Karen Sales
Sully Patrick 157 Partners Karen Sales
Raphaely Den 114 King Steven Purchasing
Baida Shelli 116 Raphaely Den Purchasing
Colmenares Karen 119 Raphaely Den Purchasing
Himuro Guy 118 Raphaely Den Purchasing
Khoo Alexander 115 Raphaely Den Purchasing
Tobias Sigal 117 Raphaely Den Purchasing
Russell John 145 King Steven Sales
Bernstein David 151 Russell John Sales
Cambrault Nanette 154 Russell John Sales
Hall Peter 152 Russell John Sales
Olsen Christopher 153 Russell John Sales
Tucker Peter 150 Russell John Sales
Tuvault Oliver 155 Russell John Sales
Vollman Shanta 123 King Steven Shipping
Bell Sarah 192 Vollman Shanta Shipping
Everett Britney 193 Vollman Shanta Shipping
Jones Vance 195 Vollman Shanta Shipping
Ladwig Renske 137 Vollman Shanta Shipping
McCain Samuel 194 Vollman Shanta Shipping
Patel Joshua 140 Vollman Shanta Shipping
Seo John 139 Vollman Shanta Shipping
Stiles Stephen 138 Vollman Shanta Shipping
Weiss Matthew 120 King Steven Shipping
Fleaur Jean 181 Weiss Matthew Shipping
Geoni Girard 183 Weiss Matthew Shipping
Landry James 127 Weiss Matthew Shipping
Markle Steven 128 Weiss Matthew Shipping
Mikkilineni Irene 126 Weiss Matthew Shipping
EMP_LAST_NAME EMP_FIRST_NAME TO_NUMBER(EMP.EMPLOYEE_ID) MGR_LAST_NAME MGR_FIRST_NAME DEPARTMENT_NAME
-------------------- -------------------- -------------------------- -------------------- -------------------- --------------------
Nayer Julia 125 Weiss Matthew Shipping
Sullivan Martha 182 Weiss Matthew Shipping
Taylor Winston 180 Weiss Matthew Shipping
Zlotkey Eleni 149 King Steven Sales
Abel Ellen 174 Zlotkey Eleni Sales
Grant Kimberely 178 Zlotkey Eleni
Hutton Alyssa 175 Zlotkey Eleni Sales
Johnson Charles 179 Zlotkey Eleni Sales
Livingston Jack 177 Zlotkey Eleni Sales
Taylor Jonathon 176 Zlotkey Eleni Sales
107 rows selected.2，RSF
Oracle 11.2中新出现的递归子查询因子化(Recursive Subquery Factoring,RSF)，在ANSI标准中这个特性的名称是递归公共表表达式。
with emp as
(select e.last_name,
e.first_name,
e.employee_id,
e.manager_id,
es.last_name mgr_last_name,
es.first_name mgr_first_name,
d.department_name
from hr.employees e
left outer join hr.departments d
on e.department_id = d.department_id
left outer join hr.employees es
on es.employee_id = e.manager_id),
emp_rsf(last_name,
first_name,
employee_id,
manager_id,
mgr_last_name,
mgr_first_name,
department_name,
lvl) as
(select e.last_name,
e.first_name,
e.employee_id,
e.manager_id,
e.mgr_last_name,
e.mgr_first_name,
e.department_name,
1 as lvl
from emp e
where e.manager_id is null
union all
select emp.last_name,
emp.first_name,
emp.employee_id,
emp.manager_id,
emp.mgr_last_name,
emp.mgr_first_name,
emp.department_name,
empr.lvl + 1 as lvl
from emp
join emp_rsf empr
on empr.employee_id = emp.manager_id) search depth first by last_name set order1
select lpad(' ', lvl * 2 - 1, ' ') || er.last_name last_name,
er.first_name,
er.department_name,
er.mgr_last_name,
er.mgr_first_name
from emp_rsf er;上面的语句使用了rsf进行了重写，其中主要的子查询是emp_rsf，递归成员通过将其与emp查询联结来引用定义性查询emp_rsf，
递归的with子句需要两个查询块：定位点成员和递归成员。这两个子查询块必须通过集合运算符union all结合到一起，union all之
前的是定位点成员，而递归成员是后面的查询。递归子查询必须应用定义子查询。
指定search depth first将会按照层级的顺序返回数据行，如果不指定search或用默认的search breadth first将会返回每一层级上的
所有数据行。
lvl是我们自己创建的相当于level伪列的功能。
col last_name for a20;
col first_name for a20;
col department_name for a20;
col mgr_last_name for a20;
col mgr_first_name for a20;
SQL> /
LAST_NAME FIRST_NAME DEPARTMENT_NAME MGR_LAST_NAME MGR_FIRST_NAME
-------------------- -------------------- -------------------- -------------------- --------------------
King Steven Executive
Cambrault Gerald Sales King Steven
Bates Elizabeth Sales Cambrault Gerald
Bloom Harrison Sales Cambrault Gerald
Fox Tayler Sales Cambrault Gerald
Kumar Sundita Sales Cambrault Gerald
Ozer Lisa Sales Cambrault Gerald
Smith William Sales Cambrault Gerald
De Haan Lex Executive King Steven
Hunold Alexander IT De Haan Lex
Austin David IT Hunold Alexander
Ernst Bruce IT Hunold Alexander
Lorentz Diana IT Hunold Alexander
Pataballa Valli IT Hunold Alexander
Errazuriz Alberto Sales King Steven
Ande Sundar Sales Errazuriz Alberto
Banda Amit Sales Errazuriz Alberto
Greene Danielle Sales Errazuriz Alberto
Lee David Sales Errazuriz Alberto
Marvins Mattea Sales Errazuriz Alberto
Vishney Clara Sales Errazuriz Alberto
Fripp Adam Shipping King Steven
Atkinson Mozhe Shipping Fripp Adam
Bissot Laura Shipping Fripp Adam
Bull Alexis Shipping Fripp Adam
Cabrio Anthony Shipping Fripp Adam
Dellinger Julia Shipping Fripp Adam
Marlow James Shipping Fripp Adam
Olson TJ Shipping Fripp Adam
Sarchand Nandita Shipping Fripp Adam
Hartstein Michael Marketing King Steven
Fay Pat Marketing Hartstein Michael
Kaufling Payam Shipping King Steven
Chung Kelly Shipping Kaufling Payam
Dilly Jennifer Shipping Kaufling Payam
Gates Timothy Shipping Kaufling Payam
Gee Ki Shipping Kaufling Payam
Mallin Jason Shipping Kaufling Payam
Perkins Randall Shipping Kaufling Payam
Philtanker Hazel Shipping Kaufling Payam
Rogers Michael Shipping Kaufling Payam
Kochhar Neena Executive King Steven
Baer Hermann Public Relations Kochhar Neena
Greenberg Nancy Finance Kochhar Neena
Chen John Finance Greenberg Nancy
Faviet Daniel Finance Greenberg Nancy
Popp Luis Finance Greenberg Nancy
Sciarra Ismael Finance Greenberg Nancy
Urman Jose Manuel Finance Greenberg Nancy
Higgins Shelley Accounting Kochhar Neena
Gietz William Accounting Higgins Shelley
Mavris Susan Human Resources Kochhar Neena
Whalen Jennifer Administration Kochhar Neena
Mourgos Kevin Shipping King Steven
Davies Curtis Shipping Mourgos Kevin
Feeney Kevin Shipping Mourgos Kevin
Grant Douglas Shipping Mourgos Kevin
Matos Randall Shipping Mourgos Kevin
OConnell Donald Shipping Mourgos Kevin
Rajs Trenna Shipping Mourgos Kevin
Vargas Peter Shipping Mourgos Kevin
Walsh Alana Shipping Mourgos Kevin
Partners Karen Sales King Steven
Doran Louise Sales Partners Karen
King Janette Sales Partners Karen
McEwen Allan Sales Partners Karen
Sewall Sarath Sales Partners Karen
Smith Lindsey Sales Partners Karen
Sully Patrick Sales Partners Karen
Raphaely Den Purchasing King Steven
Baida Shelli Purchasing Raphaely Den
Colmenares Karen Purchasing Raphaely Den
Himuro Guy Purchasing Raphaely Den
Khoo Alexander Purchasing Raphaely Den
Tobias Sigal Purchasing Raphaely Den
Russell John Sales King Steven
Bernstein David Sales Russell John
Cambrault Nanette Sales Russell John
Hall Peter Sales Russell John
Olsen Christopher Sales Russell John
Tucker Peter Sales Russell John
Tuvault Oliver Sales Russell John
Vollman Shanta Shipping King Steven
Bell Sarah Shipping Vollman Shanta
Everett Britney Shipping Vollman Shanta
Jones Vance Shipping Vollman Shanta
Ladwig Renske Shipping Vollman Shanta
McCain Samuel Shipping Vollman Shanta
Patel Joshua Shipping Vollman Shanta
Seo John Shipping Vollman Shanta
Stiles Stephen Shipping Vollman Shanta
Weiss Matthew Shipping King Steven
Fleaur Jean Shipping Weiss Matthew
Geoni Girard Shipping Weiss Matthew
Landry James Shipping Weiss Matthew
Markle Steven Shipping Weiss Matthew
Mikkilineni Irene Shipping Weiss Matthew
LAST_NAME FIRST_NAME DEPARTMENT_NAME MGR_LAST_NAME MGR_FIRST_NAME
-------------------- -------------------- -------------------- -------------------- --------------------
Nayer Julia Shipping Weiss Matthew
Sullivan Martha Shipping Weiss Matthew
Taylor Winston Shipping Weiss Matthew
Zlotkey Eleni Sales King Steven
Abel Ellen Sales Zlotkey Eleni
Grant Kimberely Zlotkey Eleni
Hutton Alyssa Sales Zlotkey Eleni
Johnson Charles Sales Zlotkey Eleni
Livingston Jack Sales Zlotkey Eleni
Taylor Jonathon Sales Zlotkey Eleni
107 rows selected.
1251
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
