ITPub博客

首页 > Linux操作系统 > Linux操作系统 > ORACLE START WITH 语句的树级结构例子

ORACLE START WITH 语句的树级结构例子

原创 Linux操作系统 作者:gaopengtttt 时间:2009-04-29 12:49:04 0 删除 编辑

原创 转载请注明出处

 

connect by 是结构化查询中用到的,其基本语法是:
select ... from tablename start by cond1
connect by cond2
where cond3;  

考虑如下语句

select *
from t_agency
where VALIDATE_STATUS = 'Y'
start with organ_id =1152
connect by parent_id = prior agency_id;

 

select parent_id,agency_id,organ_id from t_agency;

 

 

PARENT_ID   AGENCY_ID ORGAN_ID

----------- ----------- ----------------------------------------

         81                                    1

         81          82                  1152

         82          84                  1152006

         83          85                1688

         81          83                1688

          59                              1152

          60                             1152

         60          61               1152

         84          86               1152

                                       1

 

第一步查询会查找出

SQL> select *

  2  from t_agency

  3  where VALIDATE_STATUS = 'Y'

  4  and organ_id =1152

  5  ;

会出现5

  AGENCY_ID   PARENT_ID ORGAN_ID

----------- ----------- ----------------------------------------

         82          81                   1152

         59                                1152

         60                                1152

         61          60                  1152

         86          84                  1152

              2

第二步通过connect by parent_id = prior agency_id;

进行向子及衍生。

然后条件变为parent_id in(82,59,60,61,86)

及查询

SQL> select agency_id,parent_id,organ_id

  2  from t_agency

  3  where VALIDATE_STATUS = 'Y'

  4  and parent_id in(82,59,60,61,86);

 

  AGENCY_ID   PARENT_ID ORGAN_ID

----------- ----------- ----------------------------------------

         84            82               1152006

         61             60                  1152

得到2行,可以看出此两行来自于 父节点

 82          81   1152

60                 1152

第三步同样的操作条件变为parent_id in(84,61)

 

SQL> select agency_id,parent_id,organ_id

  2  from t_agency

  3  where VALIDATE_STATUS = 'Y'

  4  and parent_id in(84,61)

  5  ;

 

  AGENCY_ID   PARENT_ID ORGAN_ID

----------- ----------- ----------------------------------------

86                       84                   1152

  得到1行,可以看出此两行来自于 父节点

   84               82                 1152006

 

第四步继续田间变为parent_id =86

SQL> select agency_id,parent_id,organ_id

  2  from t_agency

  3  where VALIDATE_STATUS = 'Y'

  4  and parent_id =86

  5  ;

 

  AGENCY_ID   PARENT_ID ORGAN_ID

----------- ----------- ----------------------------------------

 

至此树形结构形成,并且中止。

 

82     59       60        61        86

84                 61

86

形成了8

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

上一篇: SQL 优化相关概念
请登录后发表评论 登录
全部评论
wxh gp_22389860 <<深入理解MySQL主从原理专栏>> 发布 可加WX了解

注册时间:2008-10-13

  • 博文量
    671
  • 访问量
    2917746