对"一个非常难的查询问题(部门上下级的关系)"之解答的完善

这是一个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

 

原文转自：http://www.ltesting.net