爱站程序员基地文章目录
- 一.start with …… connect by语句
- 二.sys_connect_by_path函数
- 三.connect_by_root 语句
- 四.NOCYCLE、CONNECT_BY_ISCYCLE语句
- 五.connect_by_isleaf语句
- 六.connect by构造数据
备注:测试数据库版本为Oracle 11g R2
这个blog我们来聊聊Oracle的层次查询
级联查询的一些语法:
select … from tablenamestart with 条件1connect by 条件2where 条件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 17566 JONES MANAGER 7839 20 27788 SCOTT ANALYST 7566 20 37876 ADAMS CLERK 7788 20 47902 FORD ANALYST 7566 20 37369 SMITH CLERK 7902 20 47698 BLAKE MANAGER 7839 30 27499 ALLEN SALESMAN 7698 30 37521 WARD SALESMAN 7698 30 37654 MARTIN SALESMAN 7698 30 37844 TURNER SALESMAN 7698 30 37900 JAMES CLERK 7698 30 37782 CLARK MANAGER 7839 10 27934 MILLER CLERK 7782 10 314 rows selectedSQL> 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 17566 JONES MANAGER 7839 20 27788 SCOTT ANALYST 7566 20 37876 ADAMS CLERK 7788 20 47902 FORD ANALYST 7566 20 37369 SMITH CLERK 7902 20 47698 BLAKE MANAGER 7839 30 27499 ALLEN SALESMAN 7698 30 37521 WARD SALESMAN 7698 30 37654 MARTIN SALESMAN 7698 30 37844 TURNER SALESMAN 7698 30 37900 JAMES CLERK 7698 30 37782 CLARK MANAGER 7839 10 27934 MILLER CLERK 7782 10 314 rows selected
用lpad进行填充，看起来更有层次感
select empno, cast(lpad(\' \', level*2-1,\' \')||ename as varchar2(20)) ename, job, mgr, deptno, levelfrom empstart 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 17566 JONES MANAGER 7839 20 27788 SCOTT ANALYST 7566 20 37876 ADAMS CLERK 7788 20 47902 FORD ANALYST 7566 20 37369 SMITH CLERK 7902 20 47698 BLAKE MANAGER 7839 30 27499 ALLEN SALESMAN 7698 30 37521 WARD SALESMAN 7698 30 37654 MARTIN SALESMAN 7698 30 37844 TURNER SALESMAN 7698 30 37900 JAMES CLERK 7698 30 37782 CLARK MANAGER 7839 10 27934 MILLER CLERK 7782 10 314 rows selected
也可以从下往上查找
查看雇员scott及其上级的相关信息
select empno, cast(lpad(\' \', level*2-1,\' \')||ename as varchar2(20)) ename, job, mgr, deptno, levelfrom empstart 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 17566 JONES MANAGER 7839 20 27839 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)) namepathfrom empstart 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)) namepath8 from emp9 start with ename = \'KING\'10 connect by prior empno = mgr;EMPNO ENAME JOB MGR DEPTNO LEVEL NAMEPATH----- -------------------- --------- ----- ------ ---------- ----------------------------------------7839 KING PRESIDENT 10 1 /KING7566 JONES MANAGER 7839 20 2 /KING/JONES7788 SCOTT ANALYST 7566 20 3 /KING/JONES/SCOTT7876 ADAMS CLERK 7788 20 4 /KING/JONES/SCOTT/ADAMS7902 FORD ANALYST 7566 20 3 /KING/JONES/FORD7369 SMITH CLERK 7902 20 4 /KING/JONES/FORD/SMITH7698 BLAKE MANAGER 7839 30 2 /KING/BLAKE7499 ALLEN SALESMAN 7698 30 3 /KING/BLAKE/ALLEN7521 WARD SALESMAN 7698 30 3 /KING/BLAKE/WARD7654 MARTIN SALESMAN 7698 30 3 /KING/BLAKE/MARTIN7844 TURNER SALESMAN 7698 30 3 /KING/BLAKE/TURNER7900 JAMES CLERK 7698 30 3 /KING/BLAKE/JAMES7782 CLARK MANAGER 7839 10 2 /KING/CLARK7934 MILLER CLERK 7782 10 3 /KING/CLARK/MILLER14 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) rootnofrom empstart 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) rootno9 from emp10 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 75667788 SCOTT ANALYST 7566 20 2 /JONES/SCOTT 75667876 ADAMS CLERK 7788 20 3 /JONES/SCOTT/ADAMS 75667902 FORD ANALYST 7566 20 2 /JONES/FORD 75667369 SMITH CLERK 7902 20 3 /JONES/FORD/SMITH 75667698 BLAKE MANAGER 7839 30 1 /BLAKE 76987499 ALLEN SALESMAN 7698 30 2 /BLAKE/ALLEN 76987521 WARD SALESMAN 7698 30 2 /BLAKE/WARD 76987654 MARTIN SALESMAN 7698 30 2 /BLAKE/MARTIN 76987844 TURNER SALESMAN 7698 30 2 /BLAKE/TURNER 76987900 JAMES CLERK 7698 30 2 /BLAKE/JAMES 76987782 CLARK MANAGER 7839 10 1 /CLARK 77827934 MILLER CLERK 7782 10 2 /CLARK/MILLER 778213 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, LEVELFROM emp2START WITH job = \'PRESIDENT\'CONNECT BY PRIOR empno = mgr;
SQL> SELECT empno, ename, job, mgr, deptno, LEVEL2 FROM emp23 START WITH job = \'PRESIDENT\'4 CONNECT BY PRIOR empno = mgr;SELECT empno, ename, job, mgr, deptno, LEVELFROM emp2START WITH job = \'PRESIDENT\'CONNECT BY PRIOR empno = mgrORA-01436: 用户数据中的 CONNECT BY 循环
加上 nocycle，让语句不循环，就可以正常运行了
CONNECT_BY_ISCYCLE 为1表示是循环的点
SELECT empno, ename, job, mgr, deptno, LEVEL,CONNECT_BY_ISCYCLE iscycleFROM emp2START WITH job = \'PRESIDENT\'CONNECT BY nocycle PRIOR empno = mgr;
SQL> SELECT empno, ename, job, mgr, deptno, LEVEL,CONNECT_BY_ISCYCLE iscycle2 FROM emp23 START WITH job = \'PRESIDENT\'4 CONNECT BY nocycle PRIOR empno = mgr;EMPNO ENAME JOB MGR DEPTNO LEVEL ISCYCLE----- ---------- --------- ----- ------ ---------- ----------7839 KING PRESIDENT 7369 10 1 07566 JONES MANAGER 7839 20 2 07788 SCOTT ANALYST 7566 20 3 07876 ADAMS CLERK 7788 20 4 07902 FORD ANALYST 7566 20 3 07369 SMITH CLERK 7902 20 4 17698 BLAKE MANAGER 7839 30 2 07499 ALLEN SALESMAN 7698 30 3 07521 WARD SALESMAN 7698 30 3 07654 MARTIN SALESMAN 7698 30 3 07844 TURNER SALESMAN 7698 30 3 07900 JAMES CLERK 7698 30 3 07782 CLARK MANAGER 7839 10 2 07934 MILLER CLERK 7782 10 3 014 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 ISLEAFfrom empstart 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 ISLEAF9 from emp10 start with ename = \'KING\'11 connect by prior empno = mgr;EMPNO ENAME JOB MGR DEPTNO LEVEL NAMEPATH ISLEAF----- -------------------- --------- ----- ------ ---------- ---------------------------------------- ----------7839 KING PRESIDENT 10 1 /KING 07566 JONES MANAGER 7839 20 2 /KING/JONES 07788 SCOTT ANALYST 7566 20 3 /KING/JONES/SCOTT 07876 ADAMS CLERK 7788 20 4 /KING/JONES/SCOTT/ADAMS 17902 FORD ANALYST 7566 20 3 /KING/JONES/FORD 07369 SMITH CLERK 7902 20 4 /KING/JONES/FORD/SMITH 17698 BLAKE MANAGER 7839 30 2 /KING/BLAKE 07499 ALLEN SALESMAN 7698 30 3 /KING/BLAKE/ALLEN 17521 WARD SALESMAN 7698 30 3 /KING/BLAKE/WARD 17654 MARTIN SALESMAN 7698 30 3 /KING/BLAKE/MARTIN 17844 TURNER SALESMAN 7698 30 3 /KING/BLAKE/TURNER 17900 JAMES CLERK 7698 30 3 /KING/BLAKE/JAMES 17782 CLARK MANAGER 7839 10 2 /KING/CLARK 07934 MILLER CLERK 7782 10 3 /KING/CLARK/MILLER 114 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----------1234SQL> --调整起始值SQL> select rownum+15 rn from dual connect by rownum<5;RN----------16171819SQL> --调整间隔SQL> select 3*rownum-9 rn from dual connect by rownum<5;RN-----------6-303SQL> --等比数列SQL> select power(2,rownum) rn from dual connect by rownum<5;RN----------24816
--用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)-------ABCDEFGHIJKLMNOPQRSTUVWXYZ26 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 xbtfrom tmp1 t1, tmp1 t2, tmp1 t3, tmp1 t4where 1 = 1and t1.n*4/5 + t2.n*3/1 + t3.n*2/1 + t4.n*3/4 = 100and t1.n + t2.n + t3.n + t4.n = 100;
SQL> with tmp1 as2 (select rownum n from dual connect by rownum <= 100-3)3 select t1.n xmj, t2.n lmj, t3.n dbt, t4.n xbt4 from tmp1 t1, tmp1 t2, tmp1 t3, tmp1 t45 where 1 = 16 and t1.n*4/5 + t2.n*3/1 + t3.n*2/1 + t4.n*3/4 = 1007 and t1.n + t2.n + t3.n + t4.n = 100;XMJ LMJ DBT XBT---------- ---------- ---------- ----------5 1 18 765 6 9 8010 2 16 7210 7 7 7615 3 14 6815 8 5 7220 4 12 6420 9 3 6825 5 10 6025 10 1 6430 1 17 5230 6 8 5635 2 15 4835 7 6 5240 3 13 4440 8 4 4845 4 11 4045 9 2 4450 5 9 3655 1 16 2855 6 7 3260 2 14 2460 7 5 2865 3 12 2065 8 3 2470 4 10 1670 9 1 2075 5 8 1280 1 15 480 6 6 885 7 4 431 rows selected
