1、列出上下级关系；
2、构造序列；
3、实现排列组合；
先看connect by 官方语法，下图示：
下面举例分析connect by 实现各种功能用法。
一、列出上下级关系
附上测试脚本如下：
点击(此处)折叠或打开
-
SQL> drop table emp;
-
-
表已删除。
-
-
SQL> create table emp(
-
2 empno number(10),
-
3 ename varchar2(20),
-
4 job varchar2(50),
-
5 mgr number(10),
-
6 deptno number(10))
-
7 ;
-
-
表已创建。
-
-
SQL> insert into emp values(7839,'king','presi','',10);
-
-
已创建 1 行。
-
-
SQL> insert into emp values(7566,'jones','manager','7839',20);
-
-
已创建 1 行。
-
-
SQL> insert into emp values(7788,'scott','analyst','7566',20);
-
-
已创建 1 行。
-
-
SQL> insert into emp values(7876,'adams','clerk','7788',20);
-
-
已创建 1 行。
-
-
SQL> insert into emp values(7902,'ford','analyst','7566',20);
-
-
已创建 1 行。
-
-
SQL> insert into emp values(7369,'smith','clerk','7902',20);
-
-
已创建 1 行。
-
-
SQL> insert into emp values(7698,'blake','manager','7839',30);
-
-
已创建 1 行。
-
-
SQL> insert into emp values(7499,'allen','salesman','7698',30);
-
-
已创建 1 行。
-
-
SQL> insert into emp values(7521,'ward','salesman','7698',30);
-
-
已创建 1 行。
-
-
SQL> insert into emp values(7654,'martin','salesman','7698',30);
-
-
已创建 1 行。
-
-
SQL> insert into emp values(7844,'turner','salesman','7698',30);
-
-
已创建 1 行。
-
-
SQL> insert into emp values(7900,'james','clerk','7698',30);
-
-
已创建 1 行。
-
-
SQL> insert into emp values(7782,'clark','manager','7839',10);
-
-
已创建 1 行。
-
-
SQL> insert into emp values(7934,'miller','clerk','7782',10);
-
-
已创建 1 行。
-
-
SQL> commit;
-
-
提交完成。
-
-
SQL> select * from emp;
-
-
EMPNO ENAME JOB MGR DEPTNO
---------- ---------- -------------------- ---------- ----------
7839 king presi 10
7566 jones manager 7839 20
7788 scott analyst 7566 20
7876 adams clerk 7788 20
7902 ford analyst 7566 20
7369 smith clerk 7902 20
7698 blake manager 7839 30
7499 allen salesman 7698 30
7521 ward salesman 7698 30
7654 martin salesman 7698 30
7844 turner salesman 7698 30EMPNO ENAME JOB MGR DEPTNO
---------- ---------- -------------------- ---------- ----------
7900 james clerk 7698 30
7782 clark manager 7839 10
7934 miller clerk 7782 10已选择14行。
-
-
SQL> select t.*,level from emp t
-
2 start with mgr is null
-
3 connect by prior empno=mgr;
-
-
EMPNO ENAME JOB MGR DEPTNO LEVEL
---------- ---------- -------------------- ---------- ---------- ----------
7839 king presi 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 3EMPNO ENAME JOB MGR DEPTNO LEVEL
---------- ---------- -------------------- ---------- ---------- ----------
7900 james clerk 7698 30 3
7782 clark manager 7839 10 2
7934 miller clerk 7782 10 3已选择14行。
-
-
SQL> column ename format a20
-
-
SQL> edit
-
已写入 file afiedt.buf
-
-
1 select t.empno,lpad(' ',level*2-1,' ')||ename ename,
-
2 job,mgr,deptno,level from emp t
-
3 start with mgr is null
-
4* connect by prior empno=mgr
-
SQL> /
EMPNO ENAME JOB MGR DEPTNO LEVEL
---------- -------------------- --------------- ---------- ---------- -----
7839 king presi 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 3EMPNO ENAME JOB MGR DEPTNO LEVEL
---------- -------------------- --------------- ---------- ---------- -----
7900 james clerk 7698 30 3
7782 clark manager 7839 10 2
7934 miller clerk 7782 10 3已选择14行。
-
- SQL>
二、构造序列
可利用rownum，level等伪例结合connect by构造序列，如下代码示：
点击(此处)折叠或打开
-
SQL> select level from dual connect by level<5;
-
-
LEVEL
-
-----
-
-
1
-
2
-
3
-
4
-
-
SQL> select rn from dual connect by rn<5;
-
select rn from dual connect by rn<5
-
*
-
第 1 行出现错误:
-
ORA-00904: "RN": 标识符无效
-
-
-
SQL> select rownum rn from dual connect by rownum<5;
-
-
RN
-
----------
-
-
1
-
2
-
3
-
4
-
- SQL>
组合：从n个不同元素里取r个元素，其可能的组合为：
点击(此处)折叠或打开
-
SQL> edit
-
已写入 file afiedt.buf
-
-
1 with t as
-
2 (select rownum rn from dual connect by level<&n+1)
-
3 select connect_by_root(rn) rrn,rn,level,
-
4 sys_connect_by_path(rn,',') xmlpath
-
5 from t
-
6 where level=&r
-
7* connect by rn<prior rn
-
SQL> /
-
输入 n 的值: 3
-
原值 2: (select rownum rn from dual connect by level<&n+1)
-
新值 2: (select rownum rn from dual connect by level<3+1)
-
输入 r 的值: 2
-
原值 6: where level=&r
-
新值 6: where level=2
-
-
RRN RN LEVEL XMLPATH
-
---------- ---------- ---------- ---------------
-
-
2 1 2 ,2,1
-
3 1 2 ,3,1
- 3 2 2 ,3,2
如果要求所有组合，就去掉上面代码中的where条件部分内容。
排列：
点击(此处)折叠或打开
-
SQL> edit
-
已写入 file afiedt.buf
-
-
1 with t as
-
2 (select rownum rn from dual connect by level<4)
-
3 select connect_by_root(rn) rrn,rn,level,
-
4 sys_connect_by_path(rn,',') xmlpath
-
5 from t
-
6 connect by nocycle rn<>prior rn
-
7* order by level,2
-
SQL> /
-
-
RRN RN LEVEL XMLPATH
-
---------- ---------- ---------- ---------------
-
-
1 1 1 ,1
-
2 2 1 ,2
-
3 3 1 ,3
-
3 1 2 ,3,1
-
2 1 2 ,2,1
-
1 2 2 ,1,2
-
3 2 2 ,3,2
-
2 3 2 ,2,3
-
1 3 2 ,1,3
-
2 1 3 ,2,3,1
-
3 1 3 ,3,2,1
-
-
RRN RN LEVEL XMLPATH
-
---------- ---------- ---------- ---------------
-
-
3 2 3 ,3,1,2
-
1 2 3 ,1,3,2
-
2 3 3 ,2,1,3
-
1 3 3 ,1,2,3
-
- 已选择15行。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/21251711/viewspace-1069342/ ，如需转载，请注明出处，否则将追究法律责任。
