Table of Contents
备注:测试数据库版本为Oracle 11g R2
这个blog我们来聊聊Oracle的层次查询
级联查询的一些语法:
select … from tablename
start with 条件1
connect by 条件2
where 条件3;
start with: 指定起始节点的条件
connect by: 指定父子行的条件关系
prior: 查询父行的限定符，格式: prior column1 = column2 or column1 = prior column2 and … ，
nocycle: 若数据表中存在循环行，那么不添加此关键字会报错，添加关键字后，便不会报错，但循环的两行只会显示其中的第一条
connect_by_iscycle: 前置条件:在使用了nocycle之后才能使用此关键字，用于表示是否是循环行，0表示否，1 表示是
connect_by_isleaf: 是否是叶子节点，0表示否，1 表示是
level: level伪列,表示层级，值越小层级越高，level=1为层级最高节点
如需要scott用户下建表及录入数据语句，可参考:
scott建表及录入数据sql脚本
这里，我们熟悉下emp的表结构
emp表中每个员工都有一个mgr(上级领导,最顶层的没有)
每个mgr其实也是emp表中的一员，通过父子ID关联，可以构造员工表的上下级关系
一.start with … connect by语句
显示员工表各个职员的级别
因为职位为PRESIDENT的mgr是空的，可以认为是最高的一级
有两种显示 上下级关系的办法，一种是通过 prior的顺序，一个是通过 parent key和child key 调换顺序。
–找到起始职位的下级以及下级的下级
connect by prior empno= mgr
connect by mgr = prior empno
–找到起始职位的上级及上级的上级
connect by prior mgr= empno
connect by empno = prior mgr
select empno, ename, job, mgr, deptno, level from emp start with job='PRESIDENT' connect by prior empno= mgr;
select empno, ename, job, mgr, deptno, level from emp start with job='PRESIDENT' connect by mgr = prior empno;
SQL> select empno, ename, job, mgr, deptno, level from emp start with job='PRESIDENT' connect by prior empno= mgr;
EMPNO ENAME JOB MGR DEPTNO LEVEL
----- ---------- --------- ----- ------ ----------
7839 KING PRESIDENT 10 1
7566 JONES MANAGER 7839 20 2
7788 SCOTT ANALYST 7566 20 3
7876 ADAMS CLERK 7788 20 4
7902 FORD ANALYST 7566 20 3
7369 SMITH CLERK 7902 20 4
7698 BLAKE MANAGER 7839 30 2
7499 ALLEN SALESMAN 7698 30 3
7521 WARD SALESMAN 7698 30 3
7654 MARTIN SALESMAN 7698 30 3
7844 TURNER SALESMAN 7698 30 3
7900 JAMES CLERK 7698 30 3
7782 CLARK MANAGER 7839 10 2
7934 MILLER CLERK 7782 10 3
14 rows selected
SQL> select empno, ename, job, mgr, deptno, level from emp start with job='PRESIDENT' connect by mgr = prior empno;
EMPNO ENAME JOB MGR DEPTNO LEVEL
----- ---------- --------- ----- ------ ----------
7839 KING PRESIDENT 10 1
7566 JONES MANAGER 7839 20 2
7788 SCOTT ANALYST 7566 20 3
7876 ADAMS CLERK 7788 20 4
7902 FORD ANALYST 7566 20 3
7369 SMITH CLERK 7902 20 4
7698 BLAKE MANAGER 7839 30 2
7499 ALLEN SALESMAN 7698 30 3
7521 WARD SALESMAN 7698 30 3
7654 MARTIN SALESMAN 7698 30 3
7844 TURNER SALESMAN 7698 30 3
7900 JAMES CLERK 7698 30 3
7782 CLARK MANAGER 7839 10 2
7934 MILLER CLERK 7782 10 3
14 rows selected
用lpad进行填充，看起来更有层次感
select empno, cast(lpad(' ', level*2-1,' ')||ename as varchar2(20)) ename, job, mgr, deptno, level
from emp
start with job='PRESIDENT' connect by prior empno= mgr;
SQL> select empno, cast(lpad(' ', level*2-1,' ')||ename as varchar2(20)) ename, job, mgr, deptno, level from emp start with job='PRESIDENT' connect by prior empno= mgr;
EMPNO ENAME JOB MGR DEPTNO LEVEL
----- -------------------- --------- ----- ------ ----------
7839 KING PRESIDENT 10 1
7566 JONES MANAGER 7839 20 2
7788 SCOTT ANALYST 7566 20 3
7876 ADAMS CLERK 7788 20 4
7902 FORD ANALYST 7566 20 3
7369 SMITH CLERK 7902 20 4
7698 BLAKE MANAGER 7839 30 2
7499 ALLEN SALESMAN 7698 30 3
7521 WARD SALESMAN 7698 30 3
7654 MARTIN SALESMAN 7698 30 3
7844 TURNER SALESMAN 7698 30 3
7900 JAMES CLERK 7698 30 3
7782 CLARK MANAGER 7839 10 2
7934 MILLER CLERK 7782 10 3
14 rows selected
也可以从下往上查找
查看雇员scott及其上级的相关信息
select empno, cast(lpad(' ', level*2-1,' ')||ename as varchar2(20)) ename, job, mgr, deptno, level
from emp
start with empno=7788 connect by empno= prior mgr;
SQL> select empno, cast(lpad(' ', level*2-1,' ')||ename as varchar2(20)) ename, job, mgr, deptno, level from emp start with empno=7788 connect by empno= prior mgr;
EMPNO ENAME JOB MGR DEPTNO LEVEL
----- -------------------- --------- ----- ------ ----------
7788 SCOTT ANALYST 7566 20 1
7566 JONES MANAGER 7839 20 2
7839 KING PRESIDENT 10 3
二.sys_connect_by_path函数
–语法：SYS_CONNECT_BY_PATH ( column , char )
–column和char可以是VARCHAR2、CHAR、NCHAR,、 NVARCHAR2中的一种
–返回值是VARCHAR2
–返回值的字符集是与column的字符集一样的
–只能用于层次查询中
从名为KING的雇员开始，查出所有下级雇员相关信息以及雇员名路径（用’/'连接）
select empno,
cast(lpad(' ', level * 2 - 1, ' ') || ename as varchar2(20)) ename,
job,
mgr,
deptno,
level,
cast(sys_connect_by_path(ename, '/') as varchar2(40)) namepath
from emp
start with ename = 'KING'
connect by prior empno = mgr;
SQL> select empno,
2 cast(lpad(' ', level * 2 - 1, ' ') || ename as varchar2(20)) ename,
3 job,
4 mgr,
5 deptno,
6 level,
7 cast(sys_connect_by_path(ename, '/') as varchar2(40)) namepath
8 from emp
9 start with ename = 'KING'
10 connect by prior empno = mgr;
EMPNO ENAME JOB MGR DEPTNO LEVEL NAMEPATH
----- -------------------- --------- ----- ------ ---------- ----------------------------------------
7839 KING PRESIDENT 10 1 /KING
7566 JONES MANAGER 7839 20 2 /KING/JONES
7788 SCOTT ANALYST 7566 20 3 /KING/JONES/SCOTT
7876 ADAMS CLERK 7788 20 4 /KING/JONES/SCOTT/ADAMS
7902 FORD ANALYST 7566 20 3 /KING/JONES/FORD
7369 SMITH CLERK 7902 20 4 /KING/JONES/FORD/SMITH
7698 BLAKE MANAGER 7839 30 2 /KING/BLAKE
7499 ALLEN SALESMAN 7698 30 3 /KING/BLAKE/ALLEN
7521 WARD SALESMAN 7698 30 3 /KING/BLAKE/WARD
7654 MARTIN SALESMAN 7698 30 3 /KING/BLAKE/MARTIN
7844 TURNER SALESMAN 7698 30 3 /KING/BLAKE/TURNER
7900 JAMES CLERK 7698 30 3 /KING/BLAKE/JAMES
7782 CLARK MANAGER 7839 10 2 /KING/CLARK
7934 MILLER CLERK 7782 10 3 /KING/CLARK/MILLER
14 rows selected
三.connect_by_root 语句
connect_by_root 查找根节点
select empno,
cast(lpad(' ', level * 2 - 1, ' ') || ename as varchar2(20)) ename,
job,
mgr,
deptno,
level,
cast(sys_connect_by_path(ename, '/') as varchar2(40)) namepath,
connect_by_root(empno) rootno
from emp
start with ename in ('JONES','BLAKE','CLARK')
connect by prior empno = mgr;
SQL> select empno,
2 cast(lpad(' ', level * 2 - 1, ' ') || ename as varchar2(20)) ename,
3 job,
4 mgr,
5 deptno,
6 level,
7 cast(sys_connect_by_path(ename, '/') as varchar2(40)) namepath,
8 connect_by_root(empno) rootno
9 from emp
10 start with ename in ('JONES','BLAKE','CLARK')
11 connect by prior empno = mgr;
EMPNO ENAME JOB MGR DEPTNO LEVEL NAMEPATH ROOTNO
----- -------------------- --------- ----- ------ ---------- ---------------------------------------- ----------
7566 JONES MANAGER 7839 20 1 /JONES 7566
7788 SCOTT ANALYST 7566 20 2 /JONES/SCOTT 7566
7876 ADAMS CLERK 7788 20 3 /JONES/SCOTT/ADAMS 7566
7902 FORD ANALYST 7566 20 2 /JONES/FORD 7566
7369 SMITH CLERK 7902 20 3 /JONES/FORD/SMITH 7566
7698 BLAKE MANAGER 7839 30 1 /BLAKE 7698
7499 ALLEN SALESMAN 7698 30 2 /BLAKE/ALLEN 7698
7521 WARD SALESMAN 7698 30 2 /BLAKE/WARD 7698
7654 MARTIN SALESMAN 7698 30 2 /BLAKE/MARTIN 7698
7844 TURNER SALESMAN 7698 30 2 /BLAKE/TURNER 7698
7900 JAMES CLERK 7698 30 2 /BLAKE/JAMES 7698
7782 CLARK MANAGER 7839 10 1 /CLARK 7782
7934 MILLER CLERK 7782 10 2 /CLARK/MILLER 7782
13 rows selected
四.NOCYCLE、CONNECT_BY_ISCYCLE语句
测试数据:
备注:改动点，原表emp表职位为PRESIDENT的mgr是空，是最高级的领导
这个地方，我把他的mgr设置为SMITH的empno 7369
drop table emp2;
create table EMP2
(
empno NUMBER(4) not null,
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2)
);
insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values ('7369', 'SMITH', 'CLERK', '7902', to_date('17-12-1980', 'dd-mm-yyyy'), '800', null, '20');
insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values ('7499', 'ALLEN', 'SALESMAN', '7698', to_date('20-02-1981', 'dd-mm-yyyy'), '1600', '300', '30');
insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values ('7521', 'WARD', 'SALESMAN', '7698', to_date('22-02-1981', 'dd-mm-yyyy'), '1250', '500', '30');
insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values ('7566', 'JONES', 'MANAGER', '7839', to_date('02-04-1981', 'dd-mm-yyyy'), '2975', null, '20');
insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values ('7654', 'MARTIN', 'SALESMAN', '7698', to_date('28-09-1981', 'dd-mm-yyyy'), '1250', '1400', '30');
insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values ('7698', 'BLAKE', 'MANAGER', '7839', to_date('01-05-1981', 'dd-mm-yyyy'), '2850', null, '30');
insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values ('7782', 'CLARK', 'MANAGER', '7839', to_date('09-06-1981', 'dd-mm-yyyy'), '2450', null, '10');
insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values ('7788', 'SCOTT', 'ANALYST', '7566', to_date('13-06-0187', 'dd-mm-yyyy'), '3000', null, '20');
insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values ('7839', 'KING', 'PRESIDENT', 7369, to_date('17-11-1981', 'dd-mm-yyyy'), '5000', null, '10');
insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values ('7844', 'TURNER', 'SALESMAN', '7698', to_date('08-09-1981', 'dd-mm-yyyy'), '1500', '0', '30');
insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values ('7876', 'ADAMS', 'CLERK', '7788', to_date('13-06-0187', 'dd-mm-yyyy'), '1100', null, '20');
insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values ('7900', 'JAMES', 'CLERK', '7698', to_date('03-12-1981', 'dd-mm-yyyy'), '950', null, '30');
insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values ('7902', 'FORD', 'ANALYST', '7566', to_date('03-12-1981', 'dd-mm-yyyy'), '3000', null, '20');
insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values ('7934', 'MILLER', 'CLERK', '7782', to_date('23-01-1982', 'dd-mm-yyyy'), '1300', null, '10');
commit;
运行最上面的sql，只是将包名从emp调整为emp2
此时报错，提示connect by循环了
SELECT empno, ename, job, mgr, deptno, LEVEL
FROM emp2
START WITH job = 'PRESIDENT'
CONNECT BY PRIOR empno = mgr;
SQL> SELECT empno, ename, job, mgr, deptno, LEVEL
2 FROM emp2
3 START WITH job = 'PRESIDENT'
4 CONNECT BY PRIOR empno = mgr;
SELECT empno, ename, job, mgr, deptno, LEVEL
FROM emp2
START WITH job = 'PRESIDENT'
CONNECT BY PRIOR empno = mgr
ORA-01436: 用户数据中的 CONNECT BY 循环
加上 nocycle，让语句不循环，就可以正常运行了
CONNECT_BY_ISCYCLE 为1表示是循环的点
SELECT empno, ename, job, mgr, deptno, LEVEL,CONNECT_BY_ISCYCLE iscycle
FROM emp2
START WITH job = 'PRESIDENT'
CONNECT BY nocycle PRIOR empno = mgr;
SQL> SELECT empno, ename, job, mgr, deptno, LEVEL,CONNECT_BY_ISCYCLE iscycle
2 FROM emp2
3 START WITH job = 'PRESIDENT'
4 CONNECT BY nocycle PRIOR empno = mgr;
EMPNO ENAME JOB MGR DEPTNO LEVEL ISCYCLE
----- ---------- --------- ----- ------ ---------- ----------
7839 KING PRESIDENT 7369 10 1 0
7566 JONES MANAGER 7839 20 2 0
7788 SCOTT ANALYST 7566 20 3 0
7876 ADAMS CLERK 7788 20 4 0
7902 FORD ANALYST 7566 20 3 0
7369 SMITH CLERK 7902 20 4 1
7698 BLAKE MANAGER 7839 30 2 0
7499 ALLEN SALESMAN 7698 30 3 0
7521 WARD SALESMAN 7698 30 3 0
7654 MARTIN SALESMAN 7698 30 3 0
7844 TURNER SALESMAN 7698 30 3 0
7900 JAMES CLERK 7698 30 3 0
7782 CLARK MANAGER 7839 10 2 0
7934 MILLER CLERK 7782 10 3 0
14 rows selected
五.connect_by_isleaf语句
connect_by_isleaf 是否叶子节点,1为是，0为否
叶子节点可以理解为最后一层
select empno,
cast(lpad(' ', level * 2 - 1, ' ') || ename as varchar2(20)) ename,
job,
mgr,
deptno,
level,
cast(sys_connect_by_path(ename, '/') as varchar2(40)) namepath,
CONNECT_BY_ISLEAF ISLEAF
from emp
start with ename = 'KING'
connect by prior empno = mgr;
SQL> select empno,
2 cast(lpad(' ', level * 2 - 1, ' ') || ename as varchar2(20)) ename,
3 job,
4 mgr,
5 deptno,
6 level,
7 cast(sys_connect_by_path(ename, '/') as varchar2(40)) namepath,
8 CONNECT_BY_ISLEAF ISLEAF
9 from emp
10 start with ename = 'KING'
11 connect by prior empno = mgr;
EMPNO ENAME JOB MGR DEPTNO LEVEL NAMEPATH ISLEAF
----- -------------------- --------- ----- ------ ---------- ---------------------------------------- ----------
7839 KING PRESIDENT 10 1 /KING 0
7566 JONES MANAGER 7839 20 2 /KING/JONES 0
7788 SCOTT ANALYST 7566 20 3 /KING/JONES/SCOTT 0
7876 ADAMS CLERK 7788 20 4 /KING/JONES/SCOTT/ADAMS 1
7902 FORD ANALYST 7566 20 3 /KING/JONES/FORD 0
7369 SMITH CLERK 7902 20 4 /KING/JONES/FORD/SMITH 1
7698 BLAKE MANAGER 7839 30 2 /KING/BLAKE 0
7499 ALLEN SALESMAN 7698 30 3 /KING/BLAKE/ALLEN 1
7521 WARD SALESMAN 7698 30 3 /KING/BLAKE/WARD 1
7654 MARTIN SALESMAN 7698 30 3 /KING/BLAKE/MARTIN 1
7844 TURNER SALESMAN 7698 30 3 /KING/BLAKE/TURNER 1
7900 JAMES CLERK 7698 30 3 /KING/BLAKE/JAMES 1
7782 CLARK MANAGER 7839 10 2 /KING/CLARK 0
7934 MILLER CLERK 7782 10 3 /KING/CLARK/MILLER 1
14 rows selected
六.connect by构造数据
--connect by构造等差数列
select rownum rn from dual connect by rownum<5;
--调整起始值
select rownum+15 rn from dual connect by rownum<5;
--调整间隔
select 3*rownum-9 rn from dual connect by rownum<5;
--等比数列
select power(2,rownum) rn from dual connect by rownum<5;
SQL> --connect by构造等差数列
SQL> select rownum rn from dual connect by rownum<5;
RN
----------
1
2
3
4
SQL> --调整起始值
SQL> select rownum+15 rn from dual connect by rownum<5;
RN
----------
16
17
18
19
SQL> --调整间隔
SQL> select 3*rownum-9 rn from dual connect by rownum<5;
RN
----------
-6
-3
0
3
SQL> --等比数列
SQL> select power(2,rownum) rn from dual connect by rownum<5;
RN
----------
2
4
8
16
--用connect by构造 26个英文字母
with t as (select ascii('A')+rownum-1 aa from dual connect by rownum<=26),
u as (select chr(aa) from t)
select * from u;
SQL> --用connect by构造 26个英文字母
SQL> with t as (select ascii('A')+rownum-1 aa from dual connect by rownum<=26),
2 u as (select chr(aa) from t)
3 select * from u;
CHR(AA)
-------
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
S
T
U
V
W
X
Y
Z
26 rows selected
百钱买鸡兔
小母鸡4块钱5只
老母鸡3块钱1只
大白兔2块钱1只
小白兔3块钱4只
花费的钱总数是100
小母鸡、老母鸡、大白兔、小白兔的总数也刚好是100
with tmp1 as
(select rownum n from dual connect by rownum <= 100-3)
select t1.n xmj, t2.n lmj, t3.n dbt, t4.n xbt
from tmp1 t1, tmp1 t2, tmp1 t3, tmp1 t4
where 1 = 1
and t1.n*4/5 + t2.n*3/1 + t3.n*2/1 + t4.n*3/4 = 100
and t1.n + t2.n + t3.n + t4.n = 100;
SQL> with tmp1 as
2 (select rownum n from dual connect by rownum <= 100-3)
3 select t1.n xmj, t2.n lmj, t3.n dbt, t4.n xbt
4 from tmp1 t1, tmp1 t2, tmp1 t3, tmp1 t4
5 where 1 = 1
6 and t1.n*4/5 + t2.n*3/1 + t3.n*2/1 + t4.n*3/4 = 100
7 and t1.n + t2.n + t3.n + t4.n = 100;
XMJ LMJ DBT XBT
---------- ---------- ---------- ----------
5 1 18 76
5 6 9 80
10 2 16 72
10 7 7 76
15 3 14 68
15 8 5 72
20 4 12 64
20 9 3 68
25 5 10 60
25 10 1 64
30 1 17 52
30 6 8 56
35 2 15 48
35 7 6 52
40 3 13 44
40 8 4 48
45 4 11 40
45 9 2 44
50 5 9 36
55 1 16 28
55 6 7 32
60 2 14 24
60 7 5 28
65 3 12 20
65 8 3 24
70 4 10 16
70 9 1 20
75 5 8 12
80 1 15 4
80 6 6 8
85 7 4 4
31 rows selected
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
