oracle树 sys_connect_by_path的应用

SELECT
max(LTRIM(SYS_CONNECT_BY_PATH(VC2AREACODE, ';'), ';')) B
FROM (
SELECT t0.VC2AREACODE, rownum RN FROM
(SELECT DISTINCT VC2AREACODE FROM CT_CUSELLRATEDETAIL_PREFIX WHERE NUMCUSELLRATEDMSGUID='997416' ORDER BY VC2AREACODE) t0
)
START WITH RN = 1
CONNECT BY RN - 1 = PRIOR RN

http://xumingrencai.iteye.com/blog/1521493
======================================================================
在SELECT命令中使用CONNECT BY 和START WITH 子句可以查询表中的树形结构关系。其命令格式如下：
SELECT ….. CONNECT BY {PRIOR 列名1=列名2|列名1=PRIOR 列名2}
[START WITH]；
其中：CONNECT BY子句说明每行资料将是按层次顺序检索，并规定将表中的资料连入树形结构的关系中。
0PRIORY运算符必须放置在连接关系的两列中的某一个的前面。对于节点间的父子关系，PRIOR去处符在一侧表示父节点，
在别一侧表示子节点，从而确定查找权结构的顺序是自顶向下还是自底向上。在连接关系中，除了可以使用列名外，
还允许使用列运算式。START WITH 子句为可选项，用来标识哪个节点作为查找树型结构的根节点。

例子：

create table test2(empno number,ename varchar2(30),managerid number);

insert into test2 values(10000,'martin',0);
insert into test2 values(10001,'binn',10000);
insert into test2 values(10002,'davit',10001);
insert into test2 values(10003,'kyte',10002);
insert into test2 values(10004,'shre',10003);
insert into test2 values(10006,'ivan',10002);
insert into test2 values(10007,'richard',10003);

select empno,ename,managerid,level,sys_connect_by_path(empno||ename,'<-') router
from test2
start with ename='martin'
--connect by empno=prior managerid
connect by prior empno=managerid
order by level desc;

select empno,ename,managerid,level,sys_connect_by_path(empno||ename,'->') router
from test2
start with ename='richard'
connect by empno=prior managerid
order by level desc;

总结：

所以，上述规则可以描述为：
01) 当操作符prior在parent前面时,得到child =>parent关系树

当prior在child前面时，得到parent =>child关系树

02) 当指定了start with时，过滤掉没有包含指定字段的所有关系;否则，不作任何过滤,显示所有关系
=-===============================================================

自己写的实例

-- Create table
create table STUDENT
(
  ID       CHAR(6),
  NAME     VARCHAR2(100),
  NUMBERID VARCHAR2(200)
)

select numberid,ltrim(max(sys_connect_by_path(name,',')),',') as name
from (
select numberid,name,
       numberid+row_number() over( order by numberid) rn,
       row_number() over(partition by numberid order by numberid) rn1
from student
)
start with rn1=1
connect by rn-1=prior rn 
group by numberid 
order by numberid 

方法2：

select wmsys.wm_concat (name)from student group by numberid