oracle 递归查询一个树形结构的菜单

关键字：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);  
  

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行。  
  

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行。  
  

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行。  
  

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愿意跟着他（这条记录比较特殊）