oracle connect by level 应用

oracle connect by level 应用
分类： oracle 2012-09-20 16:58 754人阅读 评论(0) 收藏 举报
oracleinserttreetablesql
关键字：oracle connect by level 树状结构表 
定义如下id 和 名称描述 
1— 省长 <--- 2,3 (省长管辖市长、县长) 
2— 市长 <--- 5,6 (市长管辖模范村村长和一个平民) 
3— 县长 <--- 4,7,8,9 (县长管辖镇长和三个平民) 
4— 镇长 <--- 10,11,12,13 (管辖四个平民) 
5— 村长 <--- 14,15 (管辖两个平民) 
其他(6-15)— 平民(没有管辖任何人) 


只有省长、市长和县长能处理问题，那么每个人出了问题应该首先找谁来解决？ 
省长权利最大，自己出了问题自己解决，别人也没法知道; 可以认为在关系表里省长的父节点是自己。 
Db代码   
create table person(id int primary key, description varchar2(50));   
create table relationship(child int, parent int, primary key(child,parent));   
insert into person values(1,‘省长’);   
insert into person values(2,‘市长’);   
insert into person values(3,‘县长’);   
insert into person values(4,‘镇长’);   
insert into person values(5,‘村长’);   
insert into person values(6,‘平民’);   
...//省略的都是平民   
insert into person values(15,‘平民’);   
  
insert into relationship values(1,1); //关系如下   
insert into relationship values(2,1);   
insert into relationship values(3,1);   
insert into relationship values(5,2);   
insert into relationship values(6,2);   
insert into relationship values(4,3);   
insert into relationship values(7,3);   
insert into relationship values(8,3);   
insert into relationship values(9,3);   
insert into relationship values(10,4);   
insert into relationship values(11,4);   
insert into relationship values(12,4);   
insert into relationship values(13,4);   
insert into relationship values(14,5);   
insert into relationship values(15,5);  


查看一下关系树： 
Oracle代码   
select rpad('---',(level-1)*3,'---')||child relation_tree   
from relationship   
start with child=parent   
connect by nocycle prior child=parent; --结果如下   
RELATION_TREE   
------------------   
1  
---2  
------5  
---------14  
---------15  
------6  
---3  
------4  
---------10  
---------11  
---------12  
---------13  
------7  
------8  
------9  
  
已选择15行。  


查看父子关系情况： 
Oracle代码   
select child,parent   
from relationship   
start with child=parent   
connect by nocycle prior child=parent   
order by parent; --结果如下   
 CHILD     PARENT   
---------- ----------   
         1          1  
         2          1  
         3          1  
         5          2  
         6          2  
         4          3  
         8          3  
         7          3  
         9          3  
        11          4  
        12          4  
        10          4  
        13          4  
        14          5  
        15          5  
  
已选择15行。  


下面要看一看：每个人有事时，首先找到谁来处理？不能每个人有事都找省长吧。 
下面的sql使用了oracle家的两个变态函数：first_value &　connect_by_root 
Oracle代码   
select distinct child ,first_value(parent)over(partition by child order by lv) parent   
from(   
    select connect_by_root(r.child) child, p.description descr, level lv, r.parent   
    from person p ,relationship r   
    where p.id= r.parent   
    connect by nocycle prior r.parent=r.child   
 )   
where descr in('省长', '市长', '县长')   
order by parent,child; --结果如下   
     CHILD     PARENT   
---------- ----------   
         1          1  
         2          1  
         3          1  
         5          2  
         6          2  
        14          2  
        15          2  
         4          3  
         7          3  
         8          3  
         9          3  
        10          3  
        11          3  
        12          3  
        13          3  
  
已选择15行。  


这个sql到底行不行，再加条数据看看 
Db代码   
insert into person values(333,‘县长’);   
insert into person values(555,‘村长’);   
insert into person values(666,‘平民’);    
insert into person values(777,‘平民’);   
  
insert into relationship values(333,1);   
insert into relationship values(555,333);   
insert into relationship values(666, 555);   
insert into relationship values(777,666);   
              --666这个平民有￥，777愿意跟着他（这条记录比较特殊）