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

查询结果：