sys_connect_by_path的两种用法

sys_connect_by_path的两种⽤法本⽂主要讨论sys_connect_by_path的使⽤⽅法。

支脚1、带层次关系

SQL> create table dept(deptno number,deptname varchar2(20),mgrno number);火油草

Table created.

SQL> insert into dept values(1,'总公司',null);开关柜无线测温装置

1 row created.

SQL> insert into dept values(2,'浙江分公司',1);

1 row created.智慧珠拼盘

SQL> insert into dept values(3,'杭州分公司',2);

1 row created.

SQL> commit;

土豆炮点火装置Commit complete.

SQL> select max(substr(sys_connect_by_path(deptname,','),2)) from dept connect by prior deptno=mgrno;

MAX(SUBSTR(SYS_CONNECT_BY_PATH(DEPTNAME,','),2))

--------------------------------------------------------------------------------

总公司，浙江分公司，杭州分公司

2、⾏列转换

如把⼀个表的所有列连成⼀⾏，⽤逗号分隔:

SQL> select max(substr(sys_connect_by_path(column_name,','),2))

from (select column_name,rownum rn from user_tab_columns where table_name ='DEPT')

start with rn=1 connect by rn=rownum ;

MAX(SUBSTR(SYS_CONNECT_BY_PATH(COLUMN_NAME,','),2))

--------------------------------------------------------------------------------

DEPTNO,DEPTNAME,MGRNO蒸压砖设备

本文发布于:2024-09-21 23:34:58，感谢您对本站的认可！

留言与评论（共有 0 条评论）