树形结构的概念
树形结构指的是数据元素之间存在着“一对多”的树形关系的数据结构,是一类重要的非线性数据结构。 在树形结构中,树根结点没有前驱结点,其余每个结点有且只有一个前驱结点。叶子结点没有后续结点,其余每个结点的后续节点数可以是一个也可以是多个。
层次查询的概念
层次查询(Hierarchical Queries)就是通过一定的规则,使用树的遍历(tree walking)来获取树的层次关系数据的一种方法 查询树形结构数据的父子关系,通过以下两个因素操纵树形结构的遍历 1 遍历起始位置 2 遍历扫描的规则 自上而下(Root->Branch->Leaf)自下而上(Leaf->Branch->Root) 层次查询的语法
SELECT [level], column, expr...
FROM table | view
[WHERE condition]
START WITH condition(s)
CONNECT BY [prior column1= column2 | column1 = prior column2 ...];
相关术语说明
FROM
数据来源那个表或者视图
SQL> select sys_connect_by_path(ename, '/') col1,
2 ename ,
3 sys_connect_by_path(dname, '/') col2
4 from (select * from emp e, dept d where e.deptno = d.deptno)
5 connect by prior mgr = empno ;
COL1 ENAME COL2
------------------------------ ------------------------------ ------------------------------
/SMITH SMITH /RESEARCH
/SMITH/FORD FORD /RESEARCH/RESEARCH
/SMITH/FORD/JONES JONES /RESEARCH/RESEARCH/RESEARCH
/SMITH/FORD/JONES/KING KING /RESEARCH/RESEARCH/RESEARCH/AC
COUNTING
/ALLEN ALLEN /SALES
/ALLEN/BLAKE BLAKE /SALES/SALES
/ALLEN/BLAKE/KING KING /SALES/SALES/ACCOUNTING
/WARD WARD /SALES
/WARD/BLAKE BLAKE /SALES/SALES
/WARD/BLAKE/KING KING /SALES/SALES/ACCOUNTING
/JONES JONES /RESEARCH
/JONES/KING KING /RESEARCH/ACCOUNTING
/MARTIN MARTIN /SALES
/MARTIN/BLAKE BLAKE /SALES/SALES
/MARTIN/BLAKE/KING KING /SALES/SALES/ACCOUNTING
/BLAKE BLAKE /SALES
/BLAKE/KING KING /SALES/ACCOUNTING
/CLARK CLARK /ACCOUNTING
/CLARK/KING KING /ACCOUNTING/ACCOUNTING
/SCOTT SCOTT /RESEARCH
/SCOTT/JONES JONES /RESEARCH/RESEARCH
/SCOTT/JONES/KING KING /RESEARCH/RESEARCH/ACCOUNTING
/KING KING /ACCOUNTING
/TURNER TURNER /SALES
/TURNER/BLAKE BLAKE /SALES/SALES
/TURNER/BLAKE/KING KING /SALES/SALES/ACCOUNTING
/ADAMS ADAMS /RESEARCH
/ADAMS/SCOTT SCOTT /RESEARCH/RESEARCH
/ADAMS/SCOTT/JONES JONES /RESEARCH/RESEARCH/RESEARCH
/ADAMS/SCOTT/JONES/KING KING /RESEARCH/RESEARCH/RESEARCH/AC
COUNTING
/JAMES JAMES /SALES
/JAMES/BLAKE BLAKE /SALES/SALES
/JAMES/BLAKE/KING KING /SALES/SALES/ACCOUNTING
/FORD FORD /RESEARCH
/FORD/JONES JONES /RESEARCH/RESEARCH
/FORD/JONES/KING KING /RESEARCH/RESEARCH/ACCOUNTING
/MILLER MILLER /ACCOUNTING
/MILLER/CLARK CLARK /ACCOUNTING/ACCOUNTING
/MILLER/CLARK/KING KING /ACCOUNTING/ACCOUNTING/ACCOUNT
ING
39 rows selected.
Start with子句
Start with 指定查询开始的根节点(一行或多行),所有满足条件的都将被当成是根节点如果不给定START WITH子句,那么所有的行都会被当成是根节点,然后查找其后代,通常这不是我们想要的结果.
SQL> SELECT ename, SYS_CONNECT_BY_PATH(ename, '/') col2
2 FROm scott.emp
3 CONNECT BY PRIOR empno = mgr
4 order siblings by ename ;
ENAME COL2
------ ------------------------------
FORD /FORD
SMITH /FORD/SMITH
SCOTT /SCOTT
ADAMS /SCOTT/ADAMS
ALLEN /ALLEN
JAMES /JAMES
MARTIN /MARTIN
TURNER /TURNER
WARD /WARD
MILLER /MILLER
ADAMS /ADAMS
BLAKE /BLAKE
ALLEN /BLAKE/ALLEN
JAMES /BLAKE/JAMES
MARTIN /BLAKE/MARTIN
TURNER /BLAKE/TURNER
WARD /BLAKE/WARD
CLARK /CLARK
MILLER /CLARK/MILLER
JONES /JONES
FORD /JONES/FORD
SMITH /JONES/FORD/SMITH
SCOTT /JONES/SCOTT
ADAMS /JONES/SCOTT/ADAMS
SMITH /SMITH
KING /KING
BLAKE /KING/BLAKE
ALLEN /KING/BLAKE/ALLEN
JAMES /KING/BLAKE/JAMES
MARTIN /KING/BLAKE/MARTIN
TURNER /KING/BLAKE/TURNER
WARD /KING/BLAKE/WARD
CLARK /KING/CLARK
MILLER /KING/CLARK/MILLER
JONES /KING/JONES
FORD /KING/JONES/FORD
SMITH /KING/JONES/FORD/SMITH
SCOTT /KING/JONES/SCOTT
ADAMS /KING/JONES/SCOTT/ADAMS
39 rows selected.
Connect by子句
Connect by与prior确定一个层次查询的条件和遍历的方向(prior确定)
Connect by prior column_1=column_2
CONNECT BY指定层次数据中父行和子行之间的关系,这里,关系被表示成一个表达式。当前行的字段会 和对应的父行的某个字段进行比较。 CONNECT BY 后的条件必须跟着一个PRIOR操作符 PRIOR是Oracle的一个内建操作符,只用于层次查询。当我们在层次查询的CONNECT BY条件中使用了PRIOR操作符时,位于其后的表达式被当成是当前行的父行进行比较
SQL> SELECT ename, SYS_CONNECT_BY_PATH(ename, '/') col2
2 FROm scott.emp
3 CONNECT BY PRIOR empno = mgr and empno in (SELECT EMPNO FROM EMP ) ;
ENAME COL2
------ ------------------------------
SCOTT /SCOTT
ADAMS /SCOTT/ADAMS
FORD /FORD
SMITH /FORD/SMITH
ALLEN /ALLEN
JAMES /JAMES
TURNER /TURNER
MARTIN /MARTIN
WARD /WARD
MILLER /MILLER
ADAMS /ADAMS
JONES /JONES
SCOTT /JONES/SCOTT
ADAMS /JONES/SCOTT/ADAMS
FORD /JONES/FORD
SMITH /JONES/FORD/SMITH
CLARK /CLARK
MILLER /CLARK/MILLER
BLAKE /BLAKE
ALLEN /BLAKE/ALLEN
JAMES /BLAKE/JAMES
TURNER /BLAKE/TURNER
MARTIN /BLAKE/MARTIN
WARD /BLAKE/WARD
SMITH /SMITH
KING /KING
JONES /KING/JONES
SCOTT /KING/JONES/SCOTT
ADAMS /KING/JONES/SCOTT/ADAMS
FORD /KING/JONES/FORD
SMITH /KING/JONES/FORD/SMITH
CLARK /KING/CLARK
MILLER /KING/CLARK/MILLER
BLAKE /KING/BLAKE
ALLEN /KING/BLAKE/ALLEN
JAMES /KING/BLAKE/JAMES
TURNER /KING/BLAKE/TURNER
MARTIN /KING/BLAKE/MARTIN
WARD /KING/BLAKE/WARD
39 rows selected.
注意:如果connect by 语句中没有prior关键字,则不会进行深层次的递归查询
SQL> select * from emp
2 start with mgr is null
3 connect by empno = mgr
4 ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- --------- ---------- --------- ---------- ---------- ----------
7839 KING PRESIDENT 17-NOV-81 5000 10
层次查询举例
自上向下的查询
SELECT ename
FROm scott.emp
start with ENAME='JONES'
CONNECT BY PRIOR empno = mgr ;
ENAME
------
JONES
FORD
SMITH
SCOTT
111
解析:
起始条件为 start with ENAME='JONES'
递归条件为 CONNECT BY PRIOR empno = mgr
先根据起始条件查找JONES的雇员编号 7566 (步骤1)
根据递归条件父行的雇员编号(7566)=子行的管理者编号(查找JONES的直接下属)(步骤2)
此行为类似于:
```sql
SQL> SELECT *
2 FROM EMP
3 WHERE MGR= (select EMPNO from scott.emp where ename='JONES' );
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ------ --------- ---------- --------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7902 FORD ANALYST 7566 03-DEC-81 3000 20
这样就找到了JONES的直接下级
接下来根据JONES的直接下级查找下一级 (步骤3)
其结果类似于
SQL> SELECT *
2 FROM EMP
3 WHERE EMPNO IN
4 (SELECT EMPNO
5 FROM EMP
6 WHERE MGR= (select EMPNO from scott.emp where ename='JONES' )) ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ------ --------- ---------- --------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7902 FORD ANALYST 7566 03-DEC-81 3000 20
重复操作(2)(3)
直到查找到的行没有子行为止
自下而上的查询
SQL> col conn_path for a30
SQL> select ename, sys_connect_by_path(ename, '->') conn_path
2 from emp
3 start with ename = 'MILLER'
4 connect by prior mgr = empno;
ENAME CONN_PATH
-------------------- ------------------------------
MILLER ->MILLER
CLARK ->MILLER->CLARK
KING ->MILLER->CLARK->KING
解析: (1) 根据ename 查找到到MGR
SQL> select *
2 from emp
3 where ename='MILLER';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- --------- ---------- --------- ---------- ---------- ----------
7934 MILLER CLERK 7782 23-JAN-82 1300 10
根据MGR查找直接上级 步骤(2)
SQL> select *
2 from emp
3 where empno = (select mgr from emp where ename = 'MILLER');
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- --------- ---------- --------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
根据查找到的上级的MGR(7839) 查找上级的上级 (步骤3)
SQL> select *
2 from emp
3 where empno =
4 (select mgr
5 from emp
6 where empno = (select mgr from emp where ename = 'MILLER'));
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- --------- ---------- --------- ---------- ---------- ----------
7839 KING PRESIDENT 17-NOV-81 5000 10
重复执行(2)(3)直到查询到某一行mgr为空为止 层级伪列LEVEL 伪列level 用以显示某行数据经由几次递归得来.
SQL> select level,e.*
2 from emp e
3 start with mgr is null
4 connect by prior empno = mgr ;
LEVEL EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- -------------------- --------- ---------- --------- ---------- ---------- ----------
1 7839 KING PRESIDENT 17-NOV-81 5000 10
2 7566 JONES MANAGER 7839 02-APR-81 2975 20
3 7788 SCOTT ANALYST 7566 19-APR-87 3000 20
4 7876 ADAMS CLERK 7788 23-MAY-87 1100 20
3 7902 FORD ANALYST 7566 03-DEC-81 3000 20
4 7369 SMITH CLERK 7902 17-DEC-80 800 20
2 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
3 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
3 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
3 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
3 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
3 7900 JAMES CLERK 7698 03-DEC-81 950 30
2 7782 CLARK MANAGER 7839 09-JUN-81 2450 10
3 7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
显示整个数的层级
SQL> select max(level)
2 from emp e
3 start with mgr is null
4 connect by prior empno = mgr ;
MAX(LEVEL)
----------
4
查询各个层级职员的数量
SQL> select level,count(*)
2 from emp e
3 start with mgr is null
4 connect by prior empno = mgr
5 group by level ;
LEVEL COUNT(*)
---------- ----------
1 1
2 3
4 2
3 8
使用 WHERE字句过滤数据 查询级别最低的职员信息
SQL> select *
2 from (select level lv, e.*
3 from emp e
4 start with mgr is null
5 connect by prior empno = mgr)
6 where lv = (select max(level)
7 from emp e
8 start with mgr is null
9 connect by prior empno = mgr);
LV EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- -------------------- --------- ---------- --------- ---------- ---------- ----------
4 7876 ADAMS CLERK 7788 23-MAY-87 1100 20
4 7369 SMITH CLERK 7902 17-DEC-80 800 20
level配合lpad 生成缩进
SQL> select level, empno, lpad(' ', 2 * (level - 1)) || ename ename
2 from emp
3 start with mgr is null
4 connect by prior empno = mgr;
LEVEL EMPNO ENAME
---------- ---------- --------------------
1 7839 KING
2 7566 JONES
3 7788 SCOTT
4 7876 ADAMS
3 7902 FORD
4 7369 SMITH
2 7698 BLAKE
3 7499 ALLEN
3 7521 WARD
3 7654 MARTIN
3 7844 TURNER
3 7900 JAMES
2 7782 CLARK
3 7934 MILLER
14 rows selected.
判断节点之间是否具有层次关系
SQL> select sum(cnt)
2 from (select count(*) cnt
3 from emp
4 where ename = 'JONES'
5 start with ename = 'ADAMS'
6 connect by prior mgr = empno
7 union all
8 select count(*)
9 from emp
10 where ename = 'JONES'
11 start with ename = 'ADAMS'
12 connect by prior empno = mgr);
SUM(CNT)
----------
1
将层次查询作为子查询使用
SQL> select *
2 from emp ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
SQL> update emp e
2 set e.sal = sal + 10000
3 where empno in (select empno
4 from emp
5 start with ename = 'JONES'
6 connect by prior empno = mgr);
5 rows updated.
SQL>
SQL> select *
2 from emp ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 10800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 12975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 13000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 11100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 13000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
层次查询其他特性 SYS_CONNECT_BY_PATH sys_connect_by_path(column,char) 其中,column是字符型或能自动转换成字符型的列名。char是用来连接路径的字符串。它的主要目的就是将父节点到当前节点的路径按照指定的模式展现出来 该函数只能使用在层次查询中
SQL> select ENAME,SYS_CONNECT_BY_PATH(ENAME,'->') CONN_PATH
2 from emp
3 start with ename = 'KING'
4 connect by prior empno = mgr ;
ENAME CONN_PATH
-------------------- ------------------------------
KING ->KING
JONES ->KING->JONES
SCOTT ->KING->JONES->SCOTT
ADAMS ->KING->JONES->SCOTT->ADAMS
FORD ->KING->JONES->FORD
SMITH ->KING->JONES->FORD->SMITH
BLAKE ->KING->BLAKE
ALLEN ->KING->BLAKE->ALLEN
WARD ->KING->BLAKE->WARD
MARTIN ->KING->BLAKE->MARTIN
TURNER ->KING->BLAKE->TURNER
JAMES ->KING->BLAKE->JAMES
CLARK ->KING->CLARK
MILLER ->KING->CLARK->MILLER
14 rows selected.
CONNECT_BY_ISLEAF
CONNECT_BY_ISLEAF伪列 用于判断层次查询结果集中的行是不是叶子节点。返回值0表示不是叶子节点,1表示是叶子节点
SQL> select ENAME,SYS_CONNECT_BY_PATH(ENAME,'->') CONN_PATH ,CONNECT_BY_ISLEAF
2 from emp
3 start with ename = 'KING'
4 connect by prior empno = mgr ;
ENAME CONN_PATH CONNECT_BY_ISLEAF
-------------------- ------------------------------ -----------------
KING ->KING 0
JONES ->KING->JONES 0
SCOTT ->KING->JONES->SCOTT 0
ADAMS ->KING->JONES->SCOTT->ADAMS 1
FORD ->KING->JONES->FORD 0
SMITH ->KING->JONES->FORD->SMITH 1
BLAKE ->KING->BLAKE 0
ALLEN ->KING->BLAKE->ALLEN 1
WARD ->KING->BLAKE->WARD 1
MARTIN ->KING->BLAKE->MARTIN 1
TURNER ->KING->BLAKE->TURNER 1
JAMES ->KING->BLAKE->JAMES 1
CLARK ->KING->CLARK 0
MILLER ->KING->CLARK->MILLER 1
14 rows selected.
CONNECT_BY_ROOT
如果查询为多颗树,显示每棵树的跟节点
SQL> select ename, CONNECT_BY_ROOT ename
2 from emp e
3 start with mgr = 7839
4 connect by prior empno = mgr ;
ENAME CONNECT_BY
-------------------- ----------
JONES JONES
SCOTT JONES
ADAMS JONES
FORD JONES
SMITH JONES
BLAKE BLAKE
ALLEN BLAKE
WARD BLAKE
MARTIN BLAKE
TURNER BLAKE
JAMES BLAKE
CLARK CLARK
MILLER CLARK
13 rows selected.
SIBLINGS 使用SIBLINGS关键字排序 对于层次查询的结果集,如果使用order by排序则会严格按照列的值进行排序,这可能会破坏层级表示 为了解决这个问题Oracle 使用SIBLINGS关键字对层次查询的结果集进行排序
SQL> select level, lpad(' ',2*level-1,' ')||ename conn_path ,ename
2 from emp e
3 start with mgr is null
4 connect by prior empno = mgr
5 order by ename ;
LEVEL CONN_PATH ENAME
---------- ------------------------------ --------------------
4 ADAMS ADAMS
3 ALLEN ALLEN
2 BLAKE BLAKE
2 CLARK CLARK
3 FORD FORD
3 JAMES JAMES
2 JONES JONES
1 KING KING
3 MARTIN MARTIN
3 MILLER MILLER
3 SCOTT SCOTT
4 SMITH SMITH
3 TURNER TURNER
3 WARD WARD
14 rows selected.
SQL> select level, lpad(' ',2*level-1,' ')||ename conn_path ,ename
2 from emp e
3 start with mgr is null
4 connect by prior empno = mgr
5 order SIBLINGS by ename ;
LEVEL CONN_PATH ENAME
---------- ------------------------------ --------------------
1 KING KING
2 BLAKE BLAKE
3 ALLEN ALLEN
3 JAMES JAMES
3 MARTIN MARTIN
3 TURNER TURNER
3 WARD WARD
2 CLARK CLARK
3 MILLER MILLER
2 JONES JONES
3 FORD FORD
4 SMITH SMITH
3 SCOTT SCOTT
4 ADAMS ADAMS
14 rows selected.
CONNECT_BY的死循环 有数据如下
SQL> WITH LOOP_DATA
2 AS
3 (
4 SELECT 1 node,4 parent_node FROM DUAL
5 UNION ALL
6 SELECT 2 node,1 parent_node FROM DUAL
7 UNION ALL
8 SELECT 3 node,2 parent_node FROM DUAL
9 UNION ALL
10 SELECT 4 node,3 parent_node FROM DUAL
11 )
12 select * from LOOP_DATA;
NODE PARENT_NODE
---------- -----------
1 4
2 1
3 2
4 3
现在使用节点1作为根节点查找其下属节点将会出发一个死循环 因为节点4既是1的下属节点又是1的上级节点 从而引发ora-1436错误
SQL> WITH LOOP_DATA
2 AS
3 (
4 SELECT 1 node,4 parent_node FROM DUAL
5 UNION ALL
6 SELECT 2 node,1 parent_node FROM DUAL
7 UNION ALL
8 SELECT 3 node,2 parent_node FROM DUAL
9 UNION ALL
10 SELECT 4 node,3 parent_node FROM DUAL
11 )
12 select *
13 from LOOP_DATA
14 start with node =1
15 connect by prior node = parent_node ;
ERROR:
ORA-01436: CONNECT BY loop in user data
no rows selected
为解决此问题可以使用 nocycle 关键字 来跳过循环 并使用CONNECT_BY_ISCYCLE标识出出现循环的行
SQL> WITH LOOP_DATA
2 AS
3 (
4 SELECT 1 node,4 parent_node FROM DUAL
5 UNION ALL
6 SELECT 2 node,1 parent_node FROM DUAL
7 UNION ALL
8 SELECT 3 node,2 parent_node FROM DUAL
9 UNION ALL
10 SELECT 4 node,3 parent_node FROM DUAL
11 )
12 select x.*,CONNECT_BY_ISCYCLE
13 from LOOP_DATA x
14 start with node =1
15 connect by nocycle prior node = parent_node ;
NODE PARENT_NODE CONNECT_BY_ISCYCLE
---------- ----------- ------------------
1 4 0
2 1 0
3 2 0
4 3 1
CONNECT BY的特殊用法
使用CONNECY BY 来生成数列
SQL> select level
2 from dual
3 connect by level <= 32;
LEVEL
----------
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
32 rows selected.