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

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)— 平民(没有管辖任何人)

只有省长、市长和县长能处理问题，那么每个人出了问题应该首先找谁来解决？
省长权利最大，自己出了问题自己解决，别人也没法知道; 可以认为在关系表里省长的父节点是自己。

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);
查看一下关系树：

select rpad('---',(level-1)*3,'---')||child relation_treefrom relationshipstart with child=parentconnect by nocycle prior child=parent; --结果如下RELATION_TREE------------------1---2------5---------14---------15------6---3------4---------10---------11---------12---------13------7------8------9已选择15行。
查看父子关系情况：

select child,parentfrom relationshipstart with child=parentconnect by nocycle prior child=parentorder 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

select distinct child ,first_value(parent)over(partition by child order by lv) parentfrom( 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到底行不行，再加条数据看看

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