|
|
这是一个CSDN老帖：
http://community.csdn.net/Expert/FAQ/FAQ_Index.asp?id=170559
我是抱着学习的心态看这个帖子的，下面把握学习结果总结一下。
楼主的问题是这样的：
--------------------------------------------------------------------------------
表A： id name 1 a 2 b 3 c 4 d 5 e 表B(id1,id2都与A表的id关联，是联合主外键): id1 id2 1 2 2 3 2 4 3 5 这是一个部门上下级的关系，前面的是上级，后面的下级，我想得到所有部门的列表，按照级别关系写成完整的字串，结果如下： id full_name 1 a 2 a/b 3 a/b/c 4 a/b/d 5 a/b/c/d 请问怎么写？存储过程或函数都可以，十分感谢！
---------------------------------------------------------
问题的关键是把用LEVEL关键字把层次关系搞搞清楚。
select level from table_B connect by prior id2=id1 start with id1=0;
LEVEL---------- 1 2 3 4 3
SQL> select level from table_B connect by prior id2=id1 start with id1=1;
LEVEL---------- 1 2 3 2
SQL> select level from table_B connect by prior id2=id1 start with id1=2;
LEVEL---------- 1 2 1
SQL> select level from table_B connect by prior id2=id1 start with id1=3;
LEVEL---------- 1
SQL> select level from table_B connect by prior id2=id1 start with id1=4;
LEVEL----------
SQL> select level from table_B connect by prior id2=id1 start with id1=5;
LEVEL----------
可以看出，LEVEL值表示id1领导下人员id2在id1集团所处的层数，被领导者紧跟在by prior后面。
select lpad(id2, level*length(id2), ' ') id, 2 ltrim(sys_connect_by_path(id2,'/'), '/') path 3 from Table_B 4 connect by prior id2=id1 5 start with id1=0 6 /
ID PATH-------------------------------------------------------------------------------- --------------------------------------------------------------------------------1 1 2 1/2 3 1/2/3 5 1/2/3/5 4 1/2/4
下面给出完整解答：
DROP TABLE Table_A;
Table dropped
SQL> create table Table_A (id number(4), name varchar2(20));
Table created
SQL> insert into Table_A values(1, 'a');
1 row inserted
SQL> insert into Table_A values(2, 'b');
1 row inserted
SQL> insert into Table_A values(3, 'c');
1 row inserted
SQL> insert into Table_A values(4, 'd');
1 row inserted
SQL> insert into Table_A values(5, 'e');
1 row inserted
SQL> commit;
Commit complete
SQL> DROP TABLE Table_B;
Table dropped
SQL> create table Table_B (id1 number(4), id2 number(4));
Table created
SQL> insert into table_B values(0,1);
1 row inserted
SQL> insert into Table_B values(1,2);
1 row inserted
SQL> insert into Table_B values(2,3);
1 row inserted
SQL> insert into Table_B values(2,4);
1 row inserted
SQL> insert into Table_B values(3,5);
1 row inserted
SQL> commit;
Commit complete
SQL> SELECT id2, ltrim(sys_connect_by_path(NAME, '/'), '/') path 2 from 3 (SELECT B.*, A.NAME 4 FROM Table_B B, Table_A A 5 WHERE B.id2=A.id) 6 connect by prior id2=id1 7 start with id1 = 0 8 ORDER BY id2 9 /
ID2 PATH----- -------------------------------------------------------------------------------- 1 a 2 a/b 3 a/b/c 4 a/b/d 5 a/b/c/e
|
|
