Perfect for " a very difficult query problem (the relationship between departments and subordinates) " solution

Answer | questions
This is a csdn old post:

http://community.csdn.net/Expert/FAQ/FAQ_Index.asp?id=170559

I am holding the mentality of learning to see this post, the following grasp the results of learning summary.

The problem of the landlord is this:

--------------------------------------------------------------------------------

Table A:id Name 1 a 2 B 3 C 4 D 5 e table B (Id1,id2 is associated with the ID of table A and is a federated primary foreign key): Id1 Id2 1  2 2 3 2 4 3 5 This is a department-level relationship, the front is the superior, the subordinate, I would like to get all the departments of the list, according to the rank relationship to write a complete string, the results are as follows: ID full_name 1 a 2 A/b 3 a/b/c 4 a/b/d 5 a/b/c/d How do I write? Stored procedures or functions can be, thank you very much!

---------------------------------------------------------

The key of the problem is to make the hierarchy relationship clear by the level keyword.

Select level from Table_b connect by prior ID2=ID1 to 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----------

As you can see, the level value represents the number of ID2 in the ID1 group under the leadership of ID1, followed by the leader by the prior.

Select Lpad (Id2, Level*length (ID2), ') ID, 2 LTrim (Sys_connect_by_path (Id2, '/'), '/') path 3 from Table_b 4 con Nect 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

Here's a complete answer:

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