Oracle Connect By Prior(递归查询)简洁实例
oracle中的select语句可以用START WITH...CONNECT BY PRIOR子句实现递归查询,connect by 是结构化查询中用到的,其基本语法是: 例如: 数据库表结构如下: 如图: 1.获取完整树: select * from t2; 2.获取特定子树: select * from t2 start with id = 1 connect by prior id = root_id; select * from t2 start with id = 4 connect by prior id = root_id; 3.如果connect by prior中的prior被省略,则查询将不进行深层递归。 如: select * from t2 start with root_id = 0 connect by id = root_id; select * from t2 start with id = 1 connect by id = root_id; 总结: 1、start with id= 是定义起始节点(种子),可以是id也可以是root_id,定义为root_Id查询该节点下所有的树结构,定义为id(子节点)则查询指定的树 2、connect by prior :prior的含义为先前,前一条记录。prior id=root_id 也就是前一条记录 的id等于当前记录的root_id(父id) 3、可以向下或者向上查找 4、level字段为oracle特有的层级字段,可以通过level字段查询指定的层级 select root_id,level from t2 where level=1 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |