connect by 递归子查询因子化

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) empconnect by prior emp.employee_id = emp.manager_id start with emp.manager_id is null order siblings by emp.emp_last_name;

start with 子句是用来指引从manager_id为空的那一行开始，因为这是一个在最上层只有一个人的组织层次，从而查询从 stephen king开始。

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              ShippingEMP_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                Sales107 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 order1select 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                MatthewLAST_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              Eleni107 rows selected.