Oracle中SYS_CONNECT_BY_PATH函數的妙用 ;

標籤：blog   font   div   insert   oracle   oracl   pos   ber   post   

Oracle 中SYS_CONNECT_BY_PATH函數是非常重要的函數，下面就為您介紹一個使用SYS_CONNECT_BY_PATH函數的例子，執行個體如下：

資料準備：

1 create table test (a varchar2(10),b varchar2(10));  2  3 INSERT INTO TEST (A, B) VALUES (‘1‘, ‘A‘);  4 INSERT INTO TEST (A, B) VALUES (‘1‘, ‘B‘);  5 INSERT INTO TEST (A, B) VALUES (‘2‘, ‘X‘);  6 INSERT INTO TEST (A, B) VALUES (‘2‘, ‘Y‘);  7 8 SELECT A, B FROM TEST  ;

 

 

1 SELECT A, LTRIM(MAX(SYS_CONNECT_BY_PATH(B, ‘ ‘)), ‘,‘) B2   FROM (SELECT B, A, ROW_NUMBER() OVER(PARTITION BY A ORDER BY B DESC) RN3           FROM TEST)4  START WITH RN = 15 CONNECT BY RN - 1 = PRIOR RN6        AND A = PRIOR A7  GROUP BY A;

 

Oracle中SYS_CONNECT_BY_PATH函數的妙用 ;