SYS_CONNECT_BY_PATH
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
/*
等价,相当于把每一条记录做为start with 来建立树
必须有connect by,可以没有start with
*/
select Cow,
Bull,
LPAD(' ', 6 * (Level - 1)) || Offspring Offspring,
Sex,
Birthdate,
level
from BREEDING
--start with Offspring = 'EVE'
connect by Cow = PRIOR Offspring;
select Cow,
Bull,
LPAD(' ', 6 * (Level - 1)) || Offspring Offspring,
Sex,
Birthdate,
level
from BREEDING
start with Offspring in (select offspring from BREEDING)
connect by Cow = PRIOR Offspring;
/*
Cow = PRIOR Offspring;
又可以这样理解,start with 的记录的offspring 字段= 哪些记录的cow,列出那些记录
*/
代码
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
等价,相当于把每一条记录做为start with 来建立树
必须有connect by,可以没有start with
*/
select Cow,
Bull,
LPAD(' ', 6 * (Level - 1)) || Offspring Offspring,
Sex,
Birthdate,
level
from BREEDING
--start with Offspring = 'EVE'
connect by Cow = PRIOR Offspring;
select Cow,
Bull,
LPAD(' ', 6 * (Level - 1)) || Offspring Offspring,
Sex,
Birthdate,
level
from BREEDING
start with Offspring in (select offspring from BREEDING)
connect by Cow = PRIOR Offspring;
/*
Cow = PRIOR Offspring;
又可以这样理解,start with 的记录的offspring 字段= 哪些记录的cow,列出那些记录
*/
![](https://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif)
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
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
select SYS_CONNECT_BY_PATH(DEPTNAME, '<'), DEPTNAME, deptno, mgrno, level
from dept
connect by prior deptno = mgrno;
--return 6 records
--MAX函数只是一个巧合按照字幕排列,我们应该是max(levle),或者长度最长
select MAX(SUBSTR(SYS_CONNECT_BY_PATH(DEPTNAME, '<'), 2))
from dept
connect by prior deptno = mgrno;
from dept
connect by prior deptno = mgrno;
--return 6 records
--MAX函数只是一个巧合按照字幕排列,我们应该是max(levle),或者长度最长
select MAX(SUBSTR(SYS_CONNECT_BY_PATH(DEPTNAME, '<'), 2))
from dept
connect by prior deptno = mgrno;
posted on 2010-05-17 16:51 dolphin_bobo 阅读(330) 评论(0) 编辑 收藏 举报