ORACLE SQL分割合并结果集.docVIP

ORACLE SQL分割合并结果集 SYS_CONNECT_BY_PATH函数用法 ORACLE 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 KINGJONES KINGJONESSCOTT KINGJONESSCOTTADAMS KINGJONESFORD KINGJONESFORDSMITH KINGBLAKE KINGBLAKEALLEN KINGBLAKEWARD KINGBLAKEMARTIN KINGBLAKETURNER KINGBLAKEJAMES KINGCLARK KINGCLARKMILLER 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 KINGJONES KINGJONESSCOTT KINGJONESSCOTTADAMS KINGJONESFORD KINGJONESFORDSMITH KINGBLAKE KINGBLAKEALLEN KINGBLAKEWARD KINGBLAKEMARTIN KINGBLAKETURNER KINGBLAKEJAMES KINGCLARK KINGCLARKMILLER 其实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 i