Oracle中SYS_CONNECT_BY_PATH函数的妙用 ;

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, ‘ ‘)), ‘,‘) B
2   FROM (SELECT B, A, ROW_NUMBER() OVER(PARTITION BY A ORDER BY B DESC) RN
3           FROM TEST)
4  START WITH RN = 1
5 CONNECT BY RN - 1 = PRIOR RN
6        AND A = PRIOR A
7  GROUP BY A;

 

Oracle中SYS_CONNECT_BY_PATH函数的妙用 ;

标签：blog   font   div   insert   oracle   oracl   pos   ber   post