Oracle层次查询技巧
Oracle层次查询技巧
Oracle层次查询技巧
create table emp_hire as
selectempno,mgr,ename from emp
select * from emp_hire
|
EMPNO |
MGR |
ENAME |
|
5555 |
7900 |
ggg |
|
7369 |
7902 |
SMITH |
|
7499 |
7698 |
ALLEN |
|
7521 |
7698 |
WARD |
|
7566 |
7839 |
JONES |
|
7654 |
7698 |
MA & RTIN |
|
7698 |
7839 |
BLAKE |
|
7782 |
7839 |
CLARK |
|
7788 |
7566 |
SCOTT |
|
7839 |
KING | |
|
7844 |
7698 |
TURNER |
|
7900 |
7698 |
JAMES |
|
7902 |
7566 |
FORD |
|
7934 |
7782 |
MILLER |
|
7876 |
7788 |
ADAMS |
这些数据存在着层次关系
select
lpad('*',level,'*')||e.ename ename,
e.empno,
mgr fa_id,
(select ename from emp_hire where empno=e.mgr) fa_name
from emp_hiree
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR
|
ENAME |
EMPNO |
FA_ID |
FA_NAME |
|
*KING |
7839 | ||
|
**JONES |
7566 |
7839 |
KING |
|
***SCOTT |
7788 |
7566 |
JONES |
|
****ADAMS |
7876 |
7788 |
SCOTT |
|
***FORD |
7902 |
7566 |
JONES |
|
****SMITH |
7369 |
7902 |
FORD |
|
**BLAKE |
7698 |
7839 |
KING |
|
***ALLEN |
7499 |
7698 |
BLAKE |
|
***WARD |
7521 |
7698 |
BLAKE |
|
***MA & RTIN |
7654 |
7698 |
BLAKE |
|
***TURNER |
7844 |
7698 |
BLAKE |
|
***JAMES |
7900 |
7698 |
BLAKE |
|
****ggg |
5555 |
7900 |
JAMES |
|
**CLARK |
7782 |
7839 |
KING |
|
***MILLER |
7934 |
7782 |
CLARK |
其中
start with -- this identifies all LEVEL=1 nodes in the tree
connect by -- describes how to walk from the parent nodes above to their children and their childrens children.
层次是一棵树， 又如同一个家族图谱
每一个子节电只有一个父节点；
每一个分支，最末端是叶节点；
SELECT
ename Employee,
CONNECT_BY_ISLEAF IsLeaf,
LEVEL lev,
SYS_CONNECT_BY_PATH(ename, '/') Path
FROM emp
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR
|
EMPLOYEE |
ISLEAF |
LEV |
PATH |
|
KING |
0 |
1 |
/KING |
|
JONES |
0 |
2 |
/KING/JONES |
|
SCOTT |
0 |
3 |
/KING/JONES/SCOTT |
|
ADAMS |
1 |
4 |
/KING/JONES/SCOTT/ADAMS |
|
FORD |
0 |
3 |
/KING/JONES/FORD |
|
SMITH |
1 |
4 |
/KING/JONES/FORD/SMITH |
|
BLAKE |
0 |
2 |
/KING/BLAKE |
|
ALLEN |
1 |
3 |
/KING/BLAKE/ALLEN |
|
WARD |
1 |
3 |
/KING/BLAKE/WARD |
|
MA & RTIN |
1 |
3 |
/KING/BLAKE/MA & RTIN |
|
TURNER |
1 |
3 |
/KING/BLAKE/TURNER |
|
JAMES |
0 |
3 |
/KING/BLAKE/JAMES |
|
ggg |
1 |
4 |
/KING/BLAKE/JAMES/ggg |
|
CLARK |
0 |
2 |
/KING/CLARK |
|
MILLER |
1 |
3 |
/KING/CLARK/MILLER |
检查一个层次是否存在闭循环
CONNECT_BY_ISCYCLE
找出根节点
CONNECT_BY_ROOT
SELECT
ename Employee,
CONNECT_BY_ISCYCLE,
CONNECT_BY_ROOT ename
FROM emp
START WITH MGR IS NULL
CONNECT BY NOCYCLE PRIOR EMPNO = MGR
|
EMPLOYEE |
CONNECT_BY_ISCYCLE |
CONNECT_BY_ROOTENAME |
|
KING |
0 |
KING |
|
JONES |
0 |
KING |
|
SCOTT |
0 |
KING |
|
ADAMS |
0 |
KING |
|
FORD |
0 |
KING |
|
SMITH |
0 |
KING |
|
BLAKE |
0 |
KING |
|
ALLEN |
0 |
KING |
|
WARD |
0 |
KING |
|
MA & RTIN |
0 |
KING |
|
TURNER |
0 |
KING |
|
JAMES |
0 |
KING |
|
ggg |
0 |
KING |
|
CLARK |
0 |
KING |
|
MILLER |
0 |
KING |
层次查询的过滤条件
SELECT
ename Employee,
CONNECT_BY_ISLEAF IsLeaf,
LEVEL lev,
SYS_CONNECT_BY_PATH(ename, '/') Path
FROM emp
where level<3
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR
Restriction on LEVEL in WHERE Clauses In a [NOT] IN condition in a WHERE clause, if the right-hand side of the condition is asubquery, you cannot use LEVEL on the left-hand sideof the condition. However, you can specify LEVEL in a subquery of the FROM clause to achieve the same result. For example, thefollowing statement is not valid:
SELECT employee_id, last_name FROM employees
WHERE (employee_id,LEVEL)
IN (SELECTemployee_id, 2 FROM employees)
START WITHemployee_id = 2
CONNECT BY PRIORemployee_id = manager_id;
But the following statement is valid because it encapsulates the querycontaining the
LEVEL information in the FROM clause:
SELECT v.employee_id, v.last_name, v.lev
FROM
(SELECT employee_id,last_name, LEVEL lev
FROM employees v
START WITHemployee_id = 100
CONNECT BY PRIORemployee_id = manager_id) v
WHERE(v.employee_id, v.lev) IN
(SELECT employee_id,2 FROM employees);
SIBLINGS的排序
SELECT
ename Employee,
CONNECT_BY_ISLEAF IsLeaf,
LEVEL lev,
SYS_CONNECT_BY_PATH(ename, '/') Path
FROM emp
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR
ORDER SIBLINGS BY ename desc
|
EMPLOYEE |
ISLEAF |
LEV |
PATH |
|
KING |
0 |
1 |
/KING |
|
JONES |
0 |
2 |
/KING/JONES |
|
SCOTT |
0 |
3 |
/KING/JONES/SCOTT |
|
ADAMS |
1 |
4 |
/KING/JONES/SCOTT/ADAMS |
|
FORD |
0 |
3 |
/KING/JONES/FORD |
|
SMITH |
1 |
4 |
/KING/JONES/FORD/SMITH |
|
CLARK |
0 |
2 |
/KING/CLARK |
|
MILLER |
1 |
3 |
/KING/CLARK/MILLER |
|
BLAKE |
0 |
2 |
/KING/BLAKE |
|
WARD |
1 |
3 |
/KING/BLAKE/WARD |
|
TURNER |
1 |
3 |
/KING/BLAKE/TURNER |
|
MA & RTIN |
1 |
3 |
/KING/BLAKE/MA & RTIN |
|
JAMES |
0 |
3 |
/KING/BLAKE/JAMES |
|
ggg |
1 |
4 |
/KING/BLAKE/JAMES/ggg |
|
ALLEN |
1 |
3 |
/KING/BLAKE/ALLEN |
注意：ORDER SIBLINGS By一定与start with 与 connect by一起使用
|
评论暂时关闭