Oracle
Oracle函数sys_connect_by_path 详解
语法:
Oracle函数:sys_connect_by_path 主要用于树查询(层次查询) 以及 多列转行。其语法一般为:
select … sys_connect_by_path(column_name,‘connect_symbol’) from table start with … connect by … prior
start with connect by prior用法
A:start with 子节点ID=’…’ connect by prior 子节点ID = 父节点ID
select * from 表A o start with o.org_code='10000008' connect by prior o.org_code = o.org_parent_code
按照条件org_code=‘10000008’,对’10000008’(包括自己)及其子节点进行递归查询;
B:start with 子节点ID=’…’ connect by 子节点ID = prior 父节点ID
select * from 表A o start with o.org_code='10000008' connect by o.org_code = prior o.org_parent_code
按照条件org_code=‘10000008’,对’10000008’(包括自己)及其父节点进行递归查询;
C:start with 父节点ID=’…’ connect by prior 子节点ID = 父节点ID
select * from 表A o start with o.org_parent_code='10000008' connect by prior o.org_code=o.org_parent_code
按照条件org_parent_code=‘10000008’,对’10000008’(不包括自己)子节点进行递归查询;
D:start with 父节点ID=’…’ connect by 子节点ID = prior 父节点ID
select * from 表A o start with o.org_parent_code='10000008' connect by o.org_code = prior o.org_parent_code
按照条件org_parent_code=‘10000008’,对’10000008’(包括自己)的第一代孩子们及其父节点进行递归查询;
如果有where 条件,如下
select * from 表A o where 条件 start with o.org_parent_code='10000008' connect by o.org_code = prior o.org_parent_code
执行顺序为先执行start with connect by prior,然后再按照where条件进行过滤