orcale树形结构分析以及查询

1、准备工作(orcale数据库)

添加一些静态数据:字段分别表示(编号id、名称name、父编号pid、以及位于的层数cllevel,层数可以不用,看设计数据库的需求)

使用:select * from test_tree t sql语句查出的结果如上图所示

而我们通常希望得到的结果如下图所示:第一个红色框内表示的是树的一个分支直至叶子节点,第二个红色框内表示的也是一个分支。


2、数据的查询:

要想得到上图(带红框的图)所示的结构,除了设计数据库的时候,有孩子和父亲对应关系(id和pid的对应关系)之外,还需要oracle数据库的sql语句,其语句格式如下:

SELECT  字段1,字段2,字段3  from  表名 
[where  condition3] 
[START WITH]   condition1
CONNECT BY {PRIOR 列名1=列名2|列名1=PRIOR 列名2}[condition2] 

where 语句可以省略,先说一下没有where语句的情况
简单说来是将一个树状结构存储在一张表里,比如一个表中存在两个字段: id,pid那么通过表示每一条记录的parent是谁,就可以形成一个树状结构。

conditon1是根结点的限定语句,当然可以放宽限定条件,以取得多个根结点,实际就是多棵树。   
condition2是连接条件,其中用PRIOR表示上一条记录,比如 CONNECT BY PRIOR ID=PID就是说上一条记录的ID是本条记录的PID,即本记录的父亲是上一条记录。  
conditon3是过滤条件,用于对返回的所有记录进行过滤。 
 Oracle 的树形查找语句connect by prior ... start with... 
Start with 后面跟的是开始结点即从什么位置开始查找。可以有多个起始结点 ,Connect by  连接有父子关系的两个节点 id = pid 怎么去遍历这棵树prior是关键,如果prior 在pid 这一侧(id=prior pid)则说明向父节点查找即自底向上查找,如果prior在id 这一侧(prior id= pid)则说明向子结点方向查找即自顶向下查找。  
遍历方式:先序遍历; 
 通过START WITH . . . CONNECT BY . . . 子句来实现SQL的层次查询

上图中带红色框的结果可以用下列的sql查出来:


select LPAD('第'||LEVEL||'层',LEVEL*20,'                               ') as "LEVEL",id,name,pid,cllevel from test_tree 
start with pid is null
connect by prior id = pid 


3、可以利用where对树形的结构进行过滤,例如过滤掉“太原市车辆管理所这一分支”
select LPAD('第'||LEVEL||'层',LEVEL*20,'                               ') as "LEVEL",id,name,pid,cllevel from test_tree 
where id!=1001
connect by prior id = pid 
start with pid is null

使用这样的where语句,可以过滤掉树形结构中的某个节点,但是不会影响过滤掉节点的叶子节点!
如下图所示:太原市车辆管理所不见了 ,但是它的叶子节点还在了

如果想使太原车辆管理所和它的叶子节点都不见了,那可以使用CONNECT BY 子句。CONNECT BY 子句是限定树型结构中的整个分支,既要剪除分支上的单个节点, 也要剪除其后代节点(自顶向下检索时)或前辈节点(自底向顶检索时)。
select LPAD('第'||LEVEL||'层',LEVEL*20,'                               ') as "LEVEL",id,name,pid,cllevel from test_tree 
connect by prior id = pid  and id!=1001
start with pid is null

如下图所示:太原市车管所和其子节点都不在了

4、其他有关该内容的细节:
也可以使用伪列level来对某个层以上或者向下的层数进行过滤
select level,id,name,pid,cllevel from test_tree 
connect by prior id = pid 
start with pid is null


如上图所示的层数,加入只获取2层以上的:(一般没有意义,了解一下)
select level,id,name,pid,cllevel from test_tree
where level > 2 
connect by prior id = pid 
start with pid is null

自从Oracle 9i开始,可以通过  SYS_CONNECT_BY_PATH 函数实现将父节点到当前行内容以“path”或者层次元素列表的形式显示出来。 自从Oracle 10g 中,还有其他更多关于层次查询的新特性 。例如,有的时候用户更关心的是每个层次分支中等级最低的内容。 那么你就可以利用伪列函数CONNECT_BY_ISLEAF来判断当前行是不是叶子。如果是叶子就会在伪列中显示“1”, 如果不是叶子而是一个分支(例如当前内容是其他行的父亲)就显示“0”。        
CONNECT_BY_ISLEAF的使用如下:
select CONNECT_BY_ISLEAF,LPAD('第'||LEVEL||'层',LEVEL*20,'                               ') as "LEVEL",id,name,pid,cllevel from test_tree 
start with pid is null
connect by prior id = pid


 在Oracle 10g 之前的版本中,如果在你的树中出现了环状循环(如一个孩子节点引用一个父亲节点), Oracle 就会报出一个错误提示:“ ORA-01436: CONNECT BY loop in user data”。如果不删掉对父亲的引用就无法执行查询操作。 而在 Oracle 10g 中,只要指定“NOCYCLE”就可以进行任意的查询操作。与这个关键字相关的还有一个伪列——CONNECT_BY_ISCYCLE, 如果在当前行中引用了某个父亲节点的内容并在树中出现了循环,那么该行的伪列中就会显示“1”,否则就显示“0”。  

5、最后特殊情况:

假入prior 在pid这边,可以从某个叶子节点查询一条线直到跟节点(也就是所谓的倒着查询),如下

select CONNECT_BY_ISLEAF,LPAD('第'||LEVEL||'层',LEVEL*20,'                               ') as "LEVEL",id,name,pid,cllevel from test_tree 
connect by  id =prior pid  
start with pid=1018


还有如下:

这条sql语句都可以查出来:

select CONNECT_BY_ISLEAF,LPAD('第'||LEVEL||'层',LEVEL*20,'                               ') as "LEVEL",id,name,pid,cllevel from test_tree 
start with pid is null
connect by prior id = pid  and id!=1000     (不是我所想要的结果?????????)

阅读更多
想对作者说点什么?

博主推荐

换一批

没有更多推荐了,返回首页