概述
先看看Oracle官方文档的介绍
If a table contains hierarchical data, then you can select rows in a hierarchical order using the hierarchical query clause:
hierarchical_query_clause::=
{ CONNECT BY [ NOCYCLE ] condition [ START WITH condition ]
| START WITH condition CONNECT BY [ NOCYCLE ] condition
}
start WITHspecifies the root row(s) of the hierarchy.
CONNECT BY specifies the relationship between parent rows and child rows of the hierarchy.
In a hierarchical query, one expression in condition must be qualified with the PRIOR operator to refer to the parent row. For example,
... PRIOR expr = expr or ... expr = PRIOR expr
PRIOR is a unary operator and has the same precedence as the unary + and - arithmetic operators. It evaluates the immediately following expression for the parent row of the current row in a hierarchical query.
解释一下：PRIOR是一个一元的操作，和+、-号有相同的优先级。在一个层次查询中，它会求出表达式当前行的父行。
You can further refine a hierarchical query by using the CONNECT_BY_ROOT operator to qualify a column in the select list. This operator extends the functionality of the CONNECT BY [PRIOR] condition of hierarchical queries by returning not only the immediate parent row but all ancestor rows in the hierarchy.
下面看个例子：order siblings by 定义返回同一个父亲下各个兄弟之间的顺序。
SQL> select empno,ename,mgr,level from emp start with empno=7839 connect by prio
r empno=mgr order siblings by empno;
EMPNO ENAME MGR LEVEL
---------- ---------- ---------- ----------
7839 KING 1
7566 JONES 7839 2
7788 SCOTT 7566 3
7876 ADAMS 7788 4
7902 FORD 7566 3
7369 SMITH 7902 4
7698 BLAKE 7839 2
7499 ALLEN 7698 3
7521 WARD 7698 3
7654 MARTIN 7698 3
7844 TURNER 7698 3
EMPNO ENAME MGR LEVEL
---------- ---------- ---------- ----------
7900 JAMES 7698 3
7782 CLARK 7839 2
7934 MILLER 7782 3
已选择14行。
下面我们实现一个层次结构，让关系看的更清楚
SQL> select empno,ename,mgr,level，rpad(' ',level*3) || ename tree from emp star
t with empno=7839 connect by prior empno=mgr order siblings by empno;
EMPNO ENAME MGR LEVEL TREE
---------- ---------- ---------- ---------- ------------------------------
7839 KING 1 KING
7566 JONES 7839 2 JONES
7788 SCOTT 7566 3 SCOTT
7876 ADAMS 7788 4 ADAMS
7902 FORD 7566 3 FORD
7369 SMITH 7902 4 SMITH
7698 BLAKE 7839 2 BLAKE
7499 ALLEN 7698 3 ALLEN
7521 WARD 7698 3 WARD
7654 MARTIN 7698 3 MARTIN
7844 TURNER 7698 3 TURNER
EMPNO ENAME MGR LEVEL TREE
---------- ---------- ---------- ---------- ------------------------------
7900 JAMES 7698 3 JAMES
7782 CLARK 7839 2 CLARK
7934 MILLER 7782 3 MILLER
已选择14行。
connect_by_root 必须与字段搭配使用，目的是获取根节点的信息。
connect_by_isleaf 判断当前节点是否为叶子节点，0表示为非叶子节点，1表示为叶子节点。
例子如下：
SQL> select empno,ename,mgr,level，connect_by_isleaf,connect_by_root ename rootn
ame from emp start with empno=7839 connect by prior empno=mgr order siblings by
empno;
EMPNO ENAME MGR LEVEL CONNECT_BY_ISLEAF ROOTNAME
---------- ---------- ---------- ---------- ----------------- ----------
7839 KING 1 0 KING
7566 JONES 7839 2 0 KING
7788 SCOTT 7566 3 0 KING
7876 ADAMS 7788 4 1 KING
7902 FORD 7566 3 0 KING
7369 SMITH 7902 4 1 KING
7698 BLAKE 7839 2 0 KING
7499 ALLEN 7698 3 1 KING
7521 WARD 7698 3 1 KING
7654 MARTIN 7698 3 1 KING
7844 TURNER 7698 3 1 KING
EMPNO ENAME MGR LEVEL CONNECT_BY_ISLEAF ROOTNAME
---------- ---------- ---------- ---------- ----------------- ----------
7900 JAMES 7698 3 1 KING
7782 CLARK 7839 2 0 KING
7934 MILLER 7782 3 1 KING
已选择14行。
SYS_CONNECT_BY_PATH 函数,根据遍历的路径加上分割符，看个例子就明白了
SQL> select empno,ename,mgr,level,sys_connect_by_path(ename,'/') tree from emp
tart with empno=7839 connect by prior empno=mgr order siblings by empno;
EMPNO ENAME MGR LEVEL TREE
---------- ---------- ---------- ---------- ------------------------------
7839 KING 1 /KING
7566 JONES 7839 2 /KING/JONES
7788 SCOTT 7566 3 /KING/JONES/SCOTT
7876 ADAMS 7788 4 /KING/JONES/SCOTT/ADAMS
7902 FORD 7566 3 /KING/JONES/FORD
7369 SMITH 7902 4 /KING/JONES/FORD/SMITH
7698 BLAKE 7839 2 /KING/BLAKE
7499 ALLEN 7698 3 /KING/BLAKE/ALLEN
7521 WARD 7698 3 /KING/BLAKE/WARD
7654 MARTIN 7698 3 /KING/BLAKE/MARTIN
7844 TURNER 7698 3 /KING/BLAKE/TURNER
EMPNO ENAME MGR LEVEL TREE
---------- ---------- ---------- ---------- ------------------------------
7900 JAMES 7698 3 /KING/BLAKE/JAMES
7782 CLARK 7839 2 /KING/CLARK
7934 MILLER 7782 3 /KING/CLARK/MILLER
已选择14行。
在看一个Oracle官方文档提供的例子
SELECT name, SUM(salary) "Total_Salary" FROM (
SELECT CONNECT_BY_ROOT last_name as name, Salary
FROM employees
WHERE department_id = 110
CONNECT BY PRIOR employee_id = manager_id)
GROUP BY name
ORDER BY name, "Total_Salary";
NAME Total_Salary
------------------------- ------------
Gietz 8300
Higgins 20300
King 20300
Kochhar 20300
关于层次查询学习如上，参照：Oracle® Database SQL Language Reference 11g Release 2 (11.2)
最后
以上就是娇气大侠为你收集整理的oracle 层次查询的全部内容，希望文章能够帮你解决oracle 层次查询所遇到的程序开发问题。
如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。
发表评论 取消回复