可以用level start with ....connect by
来写.
SQL> select * from tree;NODE PNODE
-------------------- --------------------
node1 node2
node2 node3
node3 node4
node4 rootSQL>
SQL> SELECT *
2 FROM (SELECT P.*, ROW_NUMBER() OVER(ORDER BY P.LL DESC) RK
3 FROM (SELECT LEVEL AS LL, TREE.*
4 FROM TREE
5 START WITH NODE = 'node2'
6 CONNECT BY NODE = PRIOR PNODE) P)
7 WHERE RK = 1
8 / LL NODE PNODE RK
---------- -------------------- -------------------- ----------
3 node4 root 1SQL>
来写.
SQL> select * from tree;NODE PNODE
-------------------- --------------------
node1 node2
node2 node3
node3 node4
node4 rootSQL>
SQL> SELECT *
2 FROM (SELECT P.*, ROW_NUMBER() OVER(ORDER BY P.LL DESC) RK
3 FROM (SELECT LEVEL AS LL, TREE.*
4 FROM TREE
5 START WITH NODE = 'node2'
6 CONNECT BY NODE = PRIOR PNODE) P)
7 WHERE RK = 1
8 / LL NODE PNODE RK
---------- -------------------- -------------------- ----------
3 node4 root 1SQL>
解决方案 »
- 日期处理的问题
- 求助!!各位大侠,帮忙看看查询速度好慢
- 触发器update时候不起作用,怎么办!
- 一个简单的sqlserver存储过程用oracle怎么写
- 高手急救:安装Oracle Data Provider For .net出错。
- rman备份问题
- oracle92和ghost冲突,请高手指点
- 过年了,散点分吧,从ORACLE版获得了不少帮助,谢谢几位斑竹的辛勤工作
- 从文本导入存储过程到oracle
- oracle9i以上就支持自动检测死锁,但是我在plsql中可以用语句查询到死锁,alert日志中没有ORA-00060的日志,详情见正文
- 请问oracle的帮助文档在哪里能找到?就象SQLSERVER中那样的帮助文档。
- 判断数据是否存在时一般使用语句select 1 from .. where.. ,它比select count(*) from .. where ..的优点在哪里?
RETURN VARCHAR2
IS
path VARCHAR2(4000);
BEGIN
path := '/' || node;
FOR cur IN (SELECT pnode from (select pnode from struct start with node = 'node2' connect by prior pnode=node)) LOOP
path := '/' || cur.pnode || path;
END LOOP;
RETURN path;
END;
/SQL> select * from struct;NODE PNODE
---------- ----------
node1 node2
node2 node3
node3 node4
node4 root
SQL> select getfullpath('node1') from dual;GETFULLPATH('NODE1')
------------------------------------------------/root/node4/node3/node2/node1SQL> select getfullpath('node2') from dual;GETFULLPATH('NODE2')
------------------------------------------------/root/node4/node3/node2SQL> select getfullpath('node3') from dual;GETFULLPATH('NODE3')
------------------------------------------------/root/node4/node3
RETURN VARCHAR2
IS
path VARCHAR2(4000);
BEGIN
path := '/' || in_node;
FOR cur IN (SELECT pnode from (select pnode from struct start with node = in_node connectby prior pnode=node)) LOOP
path := '/' || cur.pnode || path;
END LOOP;
RETURN path;
END;
/