Oracle SYS_CONNECT_BY_PATH函数使用

欢迎关注微信公众号：excelwork

参考链接：

https://docs.oracle.com/cd/B19306_01/server.102/b14200/queries003.htm#i2053935

https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions164.htm

create table tablename (id varchar (30),idd varchar(30) );

ID IDD1

TONY t2

TONY o3

TONY n4

TONY y5

123 1

123 2

123 3

SELECT id, SUBSTR(MAX(SYS_CONNECT_BY_PATH(idd, '-')), 2) NAME
  FROM (SELECT id,
               idd,
               rn,
               LEAD(rn) OVER(PARTITION BY id ORDER BY rn desc) rn1
          FROM (SELECT id, idd, ROW_NUMBER() OVER(ORDER BY id desc) rn
                  FROM table))
 START WITH rn1 IS NULLCONNECT BY rn1 = PRIOR rn
 GROUP BY id;

结果：

TONY tony

123   123

相似的函数还有wm_concat

另外，有时候会报错：不能将分隔符作为列值的一部分

修改分隔符号即可