oracle的树形结构sql一直很强大,
我想知道如何实现下面功能
比如:
id name parent_id
01 a 00
02 b 00
03 aa 01
04 bb 02
05 aaa 03
格式化的树形结构是:
a
aa
aaa
b
bb
这个效果用oracle的sql很容易实现;
我想得到这样的效果,就是只显示叶子节点的记录,其父节点用符号隔开;
也就是:
a/aa/aaa
b/ bb
根据该提示,我做了个例子:
代码:
SELECT --显示 格式化树形结构;
LPAD(' ',2*(level-1))||a.FORMCLASSNAME AS FORMCLASSNAME,
CONNECT_BY_ISLEAF as isleaf
FROM OKFORM a
where FORM_TYPE<>'0' start with PARENT='-1'
CONNECT BY PRIOR FORMCLASSNAME=PARENT
order siblings by form_order;
SELECT * FROM ( --仅显示叶子节点,不过要显示层次
select FORMCLASSNAME, SUBSTR( sys_connect_by_path(FORMCLASSNAME,'/'),2) AS X,
CONNECT_BY_ISLEAF as isleaf FROM OKFORM A
start with PARENT='-1'
CONNECT BY PRIOR FORMCLASSNAME=PARENT
order siblings by form_order ) A
WHERE ISLEAF=1