SYS_CONNECT_BY_PATH 函数用法 ORACLE

爱被打了一巴掌 2022-09-29 06:02 15阅读 0赞

SELECT ename  
FROM scott.emp  
START WITH ename = 'KING'  
CONNECT BY PRIOR empno = mgr;  
  
\-- 得到结果为：  
  
KING  
JONES  
SCOTT  
ADAMS  
FORD  
SMITH  
BLAKE  
ALLEN  
WARD  
MARTIN  
TURNER  
JAMES  
  
  
  
而：  
  
  
  
SELECT SYS\_CONNECT\_BY\_PATH(ename, '>') "Path"  
FROM scott.emp  
START WITH ename = 'KING'  
CONNECT BY PRIOR empno = mgr;  
  
  
  
\--得到结果为：  
  
  
  
>KING  
>KING>JONES  
>KING>JONES>SCOTT  
>KING>JONES>SCOTT>ADAMS  
>KING>JONES>FORD  
>KING>JONES>FORD>SMITH  
>KING>BLAKE  
>KING>BLAKE>ALLEN  
>KING>BLAKE>WARD  
>KING>BLAKE>MARTIN  
>KING>BLAKE>TURNER  
>KING>BLAKE>JAMES  
>KING>CLARK  
>KING>CLARK>MILLER  
SELECT ename  
FROM scott.emp  
START WITH ename = 'KING'  
CONNECT BY PRIOR empno = mgr;

\--得到结果为：

KING  
JONES  
SCOTT  
ADAMS  
FORD  
SMITH  
BLAKE  
ALLEN  
WARD  
MARTIN  
TURNER  
JAMES

而：

SELECT SYS\_CONNECT\_BY\_PATH(ename, '>') "Path"  
FROM scott.emp  
START WITH ename = 'KING'  
CONNECT BY PRIOR empno = mgr;

\--得到结果为：

>KING  
>KING>JONES  
>KING>JONES>SCOTT  
>KING>JONES>SCOTT>ADAMS  
>KING>JONES>FORD  
>KING>JONES>FORD>SMITH  
>KING>BLAKE  
>KING>BLAKE>ALLEN  
>KING>BLAKE>WARD  
>KING>BLAKE>MARTIN  
>KING>BLAKE>TURNER  
>KING>BLAKE>JAMES  
>KING>CLARK  
>KING>CLARK>MILLER

其实SYS\_CONNECT\_BY\_PATH这个函数是oracle9i才新提出来的！  
它一定要和connect by子句合用！  
第 一个参数是形成树形式的字段，第二个参数是父级和其子级分隔显示用的分隔符！

START WITH 代表你要开始遍历的的节点！

CONNECT BY PRIOR 是标示父子关系的对应！

如下例子：

view plaincopy to clipboardprint?  
select max(  
substr(  
sys\_connect\_by\_path(column\_name,',')  
,2)  
)  
from (select column\_name,rownum rn from user\_tab\_columns where table\_name ='AA\_TEST')  
start with rn=1 connect by rn=rownum ;  
select max(  
substr(  
sys\_connect\_by\_path(column\_name,',')  
,2)  
)  
from (select column\_name,rownum rn from user\_tab\_columns where table\_name ='AA\_TEST')  
start with rn=1 connect by rn=rownum ;

是将列用,进行分割成为一行，然后将首个，去掉，只取取最大的那个数据。

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

下面是别人的例子：

1、带层次关系

view plaincopy to clipboardprint?  
SQL> create table dept(deptno number,deptname varchar2(20),mgrno number);  
  
Table created.  
  
SQL> insert into dept values(1,'总公司',null);  
  
1 row created.  
  
SQL> insert into dept values(2,'浙江分公司',1);  
  
1 row created.  
  
SQL> insert into dept values(3,'杭州分公司',2);  
  
1 row created.  
  
SQL> commit;  
  
Commit complete.  
  
SQL> select max(substr(sys\_connect\_by\_path(deptname,','),2)) from dept connect by prior deptno=mgrno;  
  
MAX(SUBSTR(SYS\_CONNECT\_BY\_PATH(DEPTNAME,','),2))  
\--------------------------------------------------------------------------------  
总公司,浙江分公司,杭州分公司  
SQL> create table dept(deptno number,deptname varchar2(20),mgrno number);

Table created.

SQL> insert into dept values(1,'总公司',null);

1 row created.

SQL> insert into dept values(2,'浙江分公司',1);

1 row created.

SQL> insert into dept values(3,'杭州分公司',2);

1 row created.

SQL> commit;

Commit complete.

SQL> select max(substr(sys\_connect\_by\_path(deptname,','),2)) from dept connect by prior deptno=mgrno;

MAX(SUBSTR(SYS\_CONNECT\_BY\_PATH(DEPTNAME,','),2))  
\--------------------------------------------------------------------------------  
总 公司,浙江分公司,杭州分公司

2、行列转换

如把一个表的所有列连成一行，用逗号分隔:

view plaincopy to clipboardprint?  
SQL> select max(substr(sys\_connect\_by\_path(column\_name,','),2))  
from (select column\_name,rownum rn from user\_tab\_columns where table\_name ='DEPT')  
start with rn=1 connect by rn=rownum ;  
  
MAX(SUBSTR(SYS\_CONNECT\_BY\_PATH(COLUMN\_NAME,','),2))  
\--------------------------------------------------------------------------------  
DEPTNO,DEPTNAME,MGRNO