Oracle之conncet by 之练习篇 sys_connect_by_path

10、sys_connect_by_path

--sys_connect_by_path(字段名, 2个字段之间的连接符号)

--其实SYS_CONNECT_BY_PATH这个函数是oracle9i才新提出来的！

--它一定要和connect by子句合用！

select CONNECT_BY_ROOT T.ID "ROOT"

       ,T.PARENT_ID

       ,T.ID

       ,LPAD('*',10 * (level -1),'*') || T.NAMEasname

       ,CONNECT_BY_ISLEAF "ISLEAF"

       ,level

       ,SYS_CONNECT_BY_PATH(T.NAME,';') "PATH"

 from TREETABLE T

 startwith T.ID ='100000'

connectby T.PARENT_ID=prior T.ID

ROOT	PARENT_ID	ID	NAME	ISLEAF	LEVEL	PATH
100000		100000	根节点	0	1	;根节点
100000	100000	100100	**********节点1	0	2	;根节点;节点1
100000	100100	100101	********************节点3	1	3	;根节点;节点1;节点3
100000	100100	100102	********************节点4	1	3	;根节点;节点1;节点4
100000	100000	100200	**********节点2	0	2	;根节点;节点2
100000	100200	100201	********************节点5	1	3	;根节点;节点2;节点5
100000	100200	100202	********************节点6	1	3	;根节点;节点2;节点6

11、

--网上说sys_connect_by_path的连接符号不要使用逗号，oracle会报错

--但是个人实际运行，发现是可以的，没有报错，不过为了更好的使用，可以使用replace替换一下，方法如下 REPLACE(字段名,原字符,',')

select CONNECT_BY_ROOT T.ID "ROOT"

       ,T.PARENT_ID

       ,T.ID

       ,LPAD('*',10 * (level -1),'*') || T.NAMEasname

       ,CONNECT_BY_ISLEAF "ISLEAF"

       ,level

       ,replace(SYS_CONNECT_BY_PATH(T.NAME,';'),';',',') "PATH"

 from TREETABLE T

 startwith T.ID ='100000'

connectby T.PARENT_ID=prior T.ID

ROOT	PARENT_ID	ID	NAME	ISLEAF	LEVEL	PATH
100000		100000	根节点	0	1	,根节点
100000	100000	100100	**********节点1	0	2	,根节点,节点1
100000	100100	100101	********************节点3	1	3	,根节点,节点1,节点3
100000	100100	100102	********************节点4	1	3	,根节点,节点1,节点4
100000	100000	100200	**********节点2	0	2	,根节点,节点2
100000	100200	100201	********************节点5	1	3	,根节点,节点2,节点5
100000	100200	100202	********************节点6	1	3	,根节点,节点2,节点6

12、ltrim去掉开头的连接符或者用substr也可以

select CONNECT_BY_ROOT T.ID "ROOT"

       ,T.PARENT_ID

       ,T.ID

       ,LPAD('*',10 * (level -1),'*') || T.NAMEasname

       ,CONNECT_BY_ISLEAF "ISLEAF"

       ,level

       ,LTRIM(replace(SYS_CONNECT_BY_PATH(T.NAME,';'),';',','),',') "PATH"

 from TREETABLE T

 startwith T.ID ='100000'

connectby T.PARENT_ID=prior T.ID

ROOT	PARENT_ID	ID	NAME	ISLEAF	LEVEL	PATH
100000		100000	根节点	0	1	根节点
100000	100000	100100	**********节点1	0	2	根节点,节点1
100000	100100	100101	********************节点3	1	3	根节点,节点1,节点3
100000	100100	100102	********************节点4	1	3	根节点,节点1,节点4
100000	100000	100200	**********节点2	0	2	根节点,节点2
100000	100200	100201	********************节点5	1	3	根节点,节点2,节点5
100000	100200	100202	********************节点6	1	3	根节点,节点2,节点6

13、

--需求，查询出根节点到所有叶子节点的路径，而且这些叶子节点到根节点的长度是不一样的(即叶子节点不都在同一个层次上)

--这里做了一个嵌套查询，当然也有其他的方法

--像http://blog.csdn.net/ystyaoshengting/article/details/8960899 自己所转的文章中，可以用row_number()和lead()来做

select T1.ROOT

      ,T1.PARENT_ID

      ,T1.ID

      ,T1.NAME

      ,T1.ISLEAF

      ,T1.PATH

 from (select CONNECT_BY_ROOTT.ID "ROOT"

               ,T.PARENT_ID

               ,T.ID

               ,LPAD('*',10 * (level -1),'*') || T.NAMEasname

               ,CONNECT_BY_ISLEAF"ISLEAF"

               ,level

               ,LTRIM(replace(SYS_CONNECT_BY_PATH(T.NAME,';'),';',','),',') "PATH"

          from TREETABLE T

         startwith T.PARENT_IDisnull

        connectby T.PARENT_ID =prior T.ID) T1

 where T1.ISLEAF =1

ROOT	PARENT_ID	ID	NAME	ISLEAF	PATH
100000	100100	100101	********************节点3	1	根节点,节点1,节点3
100000	100100	100102	********************节点4	1	根节点,节点1,节点4
100000	100200	100201	********************节点5	1	根节点,节点2,节点5
100000	100200	100202	********************节点6	1	根节点,节点2,节点6