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
