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 |