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 语句功能.
