oracle 树形SQL

oracle树形sql查询实例分析
 
通过此SQL语句 
[sql]
select  * from tree  
 查看原始数据如下:
 

 
我们要想得到如下的一个树形查询结果如下图所示
(包含 ROOT, LEVEL, IS_LEAF,  PATH 四个字段)
 

 
可执行如下SQL语句:
[sql]
select connect_by_root(child_col) root, level ,
decode(connect_by_isleaf,0,'No',1,'Yes') is_leaf, sys_connect_by_path(child_col,'/') path  
from tree  
start with parent_col is null connect by prior child_col=parent_col;  
 
[sql]
树形查询的重点在于  start with ...  connect by prior .... 语句  
[sql]
以及 connect_by_root ,connect_by_isleaf,sys_connect_by_path这三个函数,
decode是一般常用的函数。  
 
<转:http://www.2cto.com/database/201205/131591.html>
 
 
=========================================================================
1、树形SQL结构
select
    connect by {prior col1 = col2 || col1 = prior col2 }
    [start with ]

connect by子句:指定父行和子行的关系。说明数据按照层次顺序检索,并将数据连入树形结构关系中

prior运算符:引用父行。必须放在连接关系的两列中某一列的前面,从而确定查找顺序是自上而下还是自下而上,连接关系中,可使用列名、列表达式

( prior被放置于等号前后的位置,决定着查询时的检索顺序
    置于等号前面,由父节点向子节点方向检索;置于等号后面,则从子节点向父节点方向检索)

start with 子句:为可选项,用于指定查询的根行。若省略,则表示所有满足条件的行作为根节点

(不但可以从根节点开始,而且可以定义任何节点为起始节点
    start with可指定一个或多个根节点)

2、版本新特性

在10g又增加了几个新的特性,增强了connect by子句。

从9i开始,可通过sys_connect_by_path函数实现从父节点到当前行内容以”path“或者层次元素列表的形式显示出来。sys_connect_by_path(child,'/')

connect_by_root:用在列名之前返回当前层的根节点(最高级节点的内容)

connect_by_isleaf:来判断当前行是不是叶子,如果是叶子就会在伪列中显示1

connect_by_iscycle:10g中增加对树中环状循环的处理;一旦数据中出现了循环记录(如:两个节点互为对方父节点),在10g以前版本的数据库中会错误提示”ora-01436。。“,只要指定nocycle可避免报错,且通过connect_by_iscycle属性就知道哪些节点产生了循环,如果出现循环,connect_by_iscycle伪列显示为1,否则显示为0。

 

===============================================================

相关实例:

organization、district是多对多关系,org_dis是中间表。

要查出机构下的所有城市,城市用","分隔

 select t.orgid,
        MAX(substr(sys_connect_by_path(t.name, ','), 2)) as city_name
   from (select do.id_organization orgid,
                d.name             as name,              
                row_number() over(partition by do.id_organization order by d.name desc) rn
           from district d, dis_org do
          where d.id_district = do.id_district) t
  START WITH rn = 1
 CONNECT BY rn = PRIOR rn + 1
        AND orgid = PRIOR orgid
  GROUP BY orgid

查询结果:

 

 

 

posted on 2013-11-08 15:43  快鸟  阅读(3939)  评论(0编辑  收藏  举报