This is a 9cbs old post:
http://community.9cbs.net/expert/faq/faq_index.asp?id=170559
I am looking at this post with the mentality of learning, and I will grasp the learning results summarize.
The landlord's problem 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 the ID of the A, is the combination of the owner): ID1 ID2 1 2 2 3 2 4 3 5 This is a department The relationship between the upper and lower levels, the previous level, the lower level, I want to get a list of all sectors, write a complete string according to the level relationship, as follows: ID full_name 1 a 2 A / B 3 A / B / C 4 A / B / D 5 A / B / C / D, how to write? Store procedures or functions can be, thank you very much!
-------------------------------------------------- -------
The key to the problem is to engage the level relationship with the Level keyword.
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 connection by prior id2 = id1 start with id1 = 5;
Level ------------
It can be seen that the Level value represents the number of layers at the ID1 leader at the ID1 group, followed by the leader behind by prior.
Select LPAD (ID2, Level * Length (ID2), '') ID, 2 LTRIM (Sys_Connect_by_Path (ID2, '/'), '/') Path 3 from table_b 4 connection 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 below give full solution:
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 completion
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 completion
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 connection 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
