zcgw.net
当前位置:首页 >> orAClE 递归 >>

orAClE 递归

关键字:START WITH ... CONNECT BY PRIOR select t.*,level from table_test t start with p_id = '3' connect by p_id = prior s_id; --查询p_id为3的所有子孙id select t.*,level from table_test t start with p_id = '3' connect by prior ...

oracle递归查询 Oracle中start by prior子句用法 connect by 是结构化查询中用到的,其基本语法是: select ... from tablename start with 条件1 connect by 条件2 where 条件3; 例: select * from table start with org_id = 'HBHqfWGWPy' co...

随便哪个系统,效率都不会高。 尽量避免: 1、改用集合查询 2、表结构设计时优化。比如:Code采用层次表达:01.02.aa

楼主您好 可以试试connect by prior替换成connect by nocycle prior

可以的 --层次查询,自底到顶 select t.employee_id,t.last_name,t.job_id,t.manager_id,level from employees t start with t.employee_id=101 connect by prior t.manager_id=t.employee_id; select t.empno,t.ename,t.job,t.mgr,level from e...

根节点的thelevel 应该为null,可以加个条件 where sectype.thelevel is null 。 SELECT sectype.thekey, sectype.name, sectype.thelevel FROM sectype where sectype.thelevel is null start with sectype.thekey = '8565' connect by prior s...

select ename,mgr from employee start with mgr is null connect by ename =previous mgr;

试试如下语句! select tmtaskid, tmtasknm, tmlngtask, tmobnm, tmver, tmfmnm, trparnttsk, trchildtsk, trprsseq from testctl.f9000 a, testctl.f9001 b start with a.tmtaskid = b.trchildtsk connect by prior b.trchildtsk = ParentTaskid;

相信你知道level在递归查询中的意思,除了level之外,还有一个伪列:CONNECT_BY_ISLEAF。你可以在输出的列中间加上CONNECT_BY_ISLEAF这个列,将会发现CONNECT_BY_ISLEAF为1的全是叶子节点,也就是你说的最底层。

这个链接中说oracle10g中用with as 了 http://www.itpub.net/forum.php?mod=viewthread&tid=1234453&page=1#pid14560709

网站首页 | 网站地图
All rights reserved Powered by www.zcgw.net
copyright ©right 2010-2021。
内容来自网络,如有侵犯请联系客服。zhit325@qq.com