047 connect by 例题

Statement 1:

SELECT employee_id, last_name, job_id, manager_id

FROM employees START WITH employee_id = 101

CONNECT BY PRIOR employee_id = manager_id AND manager_id != 108 ;

Statement 2:

SELECT employee_id, last_name, job_id, manager_id

FROM employees WHERE manager_id != 108 START WITH employee_id = 101

CONNECT BY PRIOR employee_id = manager_id;

Which two statements are true regarding the above SQL statements? (Choose

two.)

A. Statement 2 would not execute because the WHERE clause condition is not

allowed in a statement that has the START WITH clause.

B. The output for statement 1 would display the employee with MANAGER_ID 108

and all the employees below him or her in the hierarchy.

C. The output of statement 1 would neither display the employee with

MANAGER_ID 108 nor any employee below him or her in the hierarchy.

D. The output for statement 2 would not display the employee with MANAGER_ID

108 but it would display all the employees below him or her in the hierarchy.

Answer: CD

SQL> select a.employee_id,a.last_name,a.job_id,a.manager_id from hr.employees a
  2  start with employee_id=101
  3  connect by  prior employee_id=a.manager_id and a.manager_id !=108 ;

EMPLOYEE_ID LAST_NAME                 JOB_ID     MANAGER_ID
----------- ------------------------- ---------- ----------
        101 Kochhar                   AD_VP             100
        108 Greenberg                 FI_MGR            101
        200 Whalen                    AD_ASST           101
        203 Mavris                    HR_REP            101
        204 Baer                      PR_REP            101
        205 Higgins                   AC_MGR            101
        206 Gietz                     AC_ACCOUNT        205

7 rows selected.

SQL> select a.employee_id,a.last_name,a.job_id,a.manager_id from hr.employees a where a.manager_id !=108
  2  start with employee_id=101
  3  connect by prior employee_id=manager_id;

EMPLOYEE_ID LAST_NAME                 JOB_ID     MANAGER_ID
----------- ------------------------- ---------- ----------
        101 Kochhar                   AD_VP             100
        108 Greenberg                 FI_MGR            101
        200 Whalen                    AD_ASST           101
        203 Mavris                    HR_REP            101
        204 Baer                      PR_REP            101
        205 Higgins                   AC_MGR            101
        206 Gietz                     AC_ACCOUNT        205

直接使用 hr.employees 表做这个测试时C和D结果一样,后来发现,主要是以108为manager的员工下面没有员工了.

直接增加一行数据:

SQL> insert into employees(employee_id,last_name,email,hire_date,job_id,manager_id)
  2  select 991,‘AAAA‘,‘a@qq.com‘,hire_date,a.job_id,109 from  employees a where a.employee_id=109;

1 row created.

SQL> select a.employee_id,a.last_name,a.job_id,a.manager_id from employees a
start with employee_id=101
  3  connect by  prior employee_id=a.manager_id and a.manager_id !=108 ;

EMPLOYEE_ID LAST_NAME                 JOB_ID     MANAGER_ID
----------- ------------------------- ---------- ----------
        101 Kochhar                   AD_VP             100
        108 Greenberg                 FI_MGR            101
        200 Whalen                    AD_ASST           101
        203 Mavris                    HR_REP            101
        204 Baer                      PR_REP            101
        205 Higgins                   AC_MGR            101
        206 Gietz                     AC_ACCOUNT        205

7 rows selected.

select a.employee_id,a.last_name,a.job_id,a.manager_id from employees a where a.manager_id !=108
start with employee_id=101
  3  connect by prior employee_id=manager_id;

EMPLOYEE_ID LAST_NAME                 JOB_ID     MANAGER_ID
----------- ------------------------- ---------- ----------
        101 Kochhar                   AD_VP             100
        108 Greenberg                 FI_MGR            101
       991 AAAA                      FI_ACCOUNT        109
        200 Whalen                    AD_ASST           101
        203 Mavris                    HR_REP            101
        204 Baer                      PR_REP            101
        205 Higgins                   AC_MGR            101
        206 Gietz                     AC_ACCOUNT        205

8 rows selected.

其中109的 manager是108.第二个语句将108后续的子节点都展示了,只是过滤了manager为108的行.

而语句1则是将manager为108及后续递规的条目都过滤了,有点像 group by 里面的 having 语句功能.

047 connect by 例题,布布扣,bubuko.com

047 connect by 例题

原文：http://www.cnblogs.com/bowshy/p/3648689.html