Hierarchical Queries 级联查询(树状结构查询)

 

语法:

select * from tab [where 条件1] start with [条件2] connect by [条件3] ;

其中 connect by 与 start with 语句摆放的先后顺序不影响查询的结果，[where 条件1]可以不需要。
[where 条件1]、[条件2]、[条件3]各自作用的范围都不相同：

[where 条件1]是在根据“start with [条件2] connect by [条件3]”选择出来的记录中进行过滤，是针对单条记录的过滤， 不会影响构造树；

[条件2]限定作为搜索起始点的条件，如果是自上而下的搜索则是限定作为根节点的条件，如果是自下而上的搜索则是限定作为叶子节点的条件；

[条件3]指定构造树的条件，以及对树分支的过滤条件，在这里执行的过滤会把符合条件的记录及其下的所有子节点都过滤掉；

select * from t
where t.id!=123
start with p_id = 0
connect by prior id = p_id;

prior有两种写法,connect by prior id = p_id 或 connect by p_id = prior id     

前一种写法表示采用自上而下的搜索方式（先找父节点然后找子节点）
后一种写法表示采用自下而上的搜索方式（先找叶子节点然后找父节点）

CONNECT_BY_ROOT 用在列名之前用于返回当前层的根节点 ,CONNECT_BY_ROOT name p_name /*返回该行的根节点的列的值*/
SYS_CONNECT_BY_PATH(value,'>')函数，我们可以得到 层次结构或者说树状结构 的路径
value是要连接的值(列,表达式),'>'是连接符
CONNECT_BY_ISLEAF 伪列 (0-存在孩子,1-leaf),LEAF（叶子），即没有孩子节点
LEVEL 伪列

引入结构化查询后，SQL语句的执行顺序问题，根据Oracle文档，先后是：

1）JOIN，无论用的是JOIN ON的写法，还是在WHERE中做的关联

2）CONNECT BY

3）其它的WHERE条件

所以懒得记...还是套一张表好了,先where查出结果集,作为子表再构造树

select  b.parentid p_id,

       CONNECT_BY_ROOT  name p_name,

       b.numid cnt_child,

        level, /*lpad('└ '||dataid,(4*level),' '),*/

       connect_by_isleaf isleaf,

       sys_connect_by_path(dataid,  '>') all_path

   from ( select a.parentid,

               a.name,

               a.dataid,

               row_number()  over(partition by a.parentid order by a.sequence) numid

           from datadic a) b

  where  connect_by_isleaf = 1

  start  with b.numid = 1

connect  by prior numid = b.numid - 1

        and prior parentid = parentid;

 P_ID P_NAME         CNT_CHILD LEVEL ISLEAF ALL_PATH
----- ------------- ---------- ----- ------ ------------------------------------
    0 A                      2     2      1 >1>2
    1 A--1                   2     2      1 >3>4
    3 A--1--1                1     1      1 >5
    5 A--1--1--1             2     2      1 >140>150
  140 A--1--1--1--1          9     9      1 >141>142>143>144>145>146>147>148>149
  150 A--1--1--2--1          9     9      1 >151>152>153>154>155>156>157>158>159

其他:

select rownum from dual connect by level < 10000; /* 插表时很好用 */

可以实现行转列

select  ww.*,sys_connect_by_path(name ,',') from

(

with

w1  as ( select 'a1' as name from dual

union  select 'b2' from dual

union  select 'c3' from dual

)

select  w1.* ,rownum id from w1 

) ww

start  with id = 1

connect  by prior id = id - 1;

NAME         ID SYS_CONNECT_BY_PATH(NAME,',')
---- ---------- --------------------------------
a1            1 ,a1
b2            2 ,a1,b2
c3            3 ,a1,b2,c3

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28859270/viewspace-773430/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28859270/viewspace-773430/