Oracle SYS_CONNECT_BY_PATH函数使用

2017-12-07 20:10:49来源:CSDN作者:TheLittlePython人点击

分享

参考链接:

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 16 123 27 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 guofeng)) START WITH rn1 IS NULLCONNECT BY rn1 = PRIOR rn GROUP BY id;

目的是汇总父节点下面的所有子节点,取最长

结果如下:

   	ID	NAME1	123	1,2,32	TONY	t,o,n,y
还有wmsys.wm_concat函数

   

微信扫一扫

第七城市微信公众平台