如果打算列出所有的员工和他的经理。可以完成一个自外连接，如下：
SELECT e_top.lname, e_2.lname, e_3.lname, e_4.lname
FROM employee e_top LEFT OUTER JOIN employee e_2
ON e_top.emp_id = e_2.manager_emp_id
LEFT OUTER JOIN employee e_3
ON e_2.emp_id = e_3.manager_emp_id
LEFT OUTER JOIN employee e_4
ON e_3.emp_id = e_4.manager_emp_id
WHERE e_top.manager_emp_id IS NULL;
LNAME LNAME LNAME LNAME
-------------------- -------------------- -------------------- ------
KING JONES FORD SMITH
KING JONES SCOTT ADAMS
KING BLAKE TURNER
KING BLAKE ALLEN
KING BLAKE WARD
KING CLARK MILLER
KING BLAKE MARTIN
KING BLAKE JAMES
8 rows selected.
|
这种类型的查询有缺点。第一，需要知道组织结构有多少层，而且，对于一个有四层的employee表，你需要把四个表实例连接在一起，考虑一下如果表中有20层的情况。
为了解决这些问题，oracle提供了对于ANSI SQL的一些扩展。
START WITH ... CONNECT BY 子句
PRIOR操作符
LEVEL伪列
语法如下：
[[START WITH condition1] CONNECT BY condition2]
START WITH condition1 指定根元素。所有满足condition1 条件的行都被认作是根。
condition1 可以包含子查询。
CONNECT BY condition2 指定父行与子行的关系。condition2必须包含一个PRIOR
操作符，用来确定父行里的列，condition2不能包含子查询。
SELECT lname, emp_id, manager_emp_id
FROM employee
START WITH manager_emp_id IS NULL
CONNECT BY PRIOR emp_id = manager_emp_id;
LNAME EMP_ID MANAGER_EMP_ID
-------------------- ---------- --------------
KING 7839
JONES 7566 7839
SCOTT 7788 7566
ADAMS 7876 7788
FORD 7902 7566
SMITH 7369 7902
BLAKE 7698 7839
ALLEN 7499 7698
WARD 7521 7698
MARTIN 7654 7698
TURNER 7844 7698
JAMES 7900 7698
CLARK 7782 7839
MILLER 7934 7782
14 rows selected.
|
考虑如下查询，以一个工作时间最长的员工为根：
SELECT lname, emp_id, manager_emp_id
FROM employee
START WITH hire_date = (SELECT MIN(hire_date) FROM employee)
CONNECT BY manager_emp_id = PRIOR emp_id;
LNAME EMP_ID MANAGER_EMP_ID
-------------------- ---------- --------------
BLAKE 7698 7839
ALLEN 7499 7698
WARD 7521 7698
MARTIN 7654 7698
TURNER 7844 7698
JAMES 7900 7698
6 rows selected.
|
posted on 2007-10-11 17:47
tianjuchuan 阅读(212)
评论(0) 编辑 收藏 引用 所属分类:
sql