通过连接实现层级函数(2)

外连接显示那些叶子节点

 

SQL> r 1 select l1.id as l1_id,l1.name as parent, 2 l2.id as l2_id,l2.name as child, 3 l1.parent_id as l1_pid, 4 l2.parent_id as l2_pid 5 from direct l1,direct l2 6 where l1.id = l2.parent_id(+) 7*L1_ID PARENT L2_ID CHILD L1_PID L2_PID----- -------------------- ----- -------------------- ------ ------ 1 oracle 2 assistants 1 1 oracle 3 BIN 1 1 oracle 4 RDBMS 1 4 RDBMS 5 ADMIN 1 4 4 RDBMS 6 demo 1 4 4 RDBMS 7 install 1 4 4 RDBMS 8 jlib 1 4 5 ADMIN 9 catcr.sql 4 5 5 ADMIN 10 catawrvw.sql 4 5 8 jlib 4 3 BIN 1 2 assistants 1 10 catawrvw.sql 5 6 demo 4 7 install 4 9 catcr.sql 516 rows selected.

 

通过下面的检索可以看出由于这种写法意味着只有三层，两个连接，但是在parent列中仍然有oracle的子节点，rdbms说明

 

SQL> select l1.id as l1_id,l1.name as parent, 2 l2.id as l2_id,l2.name as child, 3 l3.id as l3_id,l3.name as sub_child, 4 l1.parent_id as l1_pid, 5 l2.parent_id as l2_pid, 6 l3.parent_id as l3_pid 7 from direct l1,direct l2,direct l3 8 where l1.id = l2.parent_id(+) 9 and l2.id = l3.parent_id(+) 10 ;L1_ID PARENT L2_ID CHILD L3_ID SUB_CHILD L1_PID L2_PID L3_PID----- ------------ ----- ------------- ----- -------------- ------ ------ ------ 1 oracle 4 RDBMS 5 ADMIN 1 4 1 oracle 4 RDBMS 6 demo 1 4 1 oracle 4 RDBMS 7 install 1 4 1 oracle 4 RDBMS 8 jlib 1 4 4 RDBMS 5 ADMIN 9 catcr.sql 1 4 5 4 RDBMS 5 ADMIN 10 catawrvw.sql 1 4 5 9 catcr.sql 5 7 install 4 6 demo 4 10 catawrvw.sql 5 2 assistants 1 3 BIN 1 8 jlib 4 4 RDBMS 8 jlib 1 4 1 oracle 3 BIN 1 5 ADMIN 10 catawrvw.sql 4 5 4 RDBMS 7 install 1 4 5 ADMIN 9 catcr.sql 4 5 1 oracle 2 assistants 1 4 RDBMS 6 demo 1 420 rows selected.

 

 SQL> r 1 select l1.id as l1_id,l1.name as parent, 2 l2.id as l2_id,l2.name as child, 3 l3.id as l3_id,l3.name as sub_child, 4 l4.id as l4_id,l4.name as sub2_child, 5 l1.parent_id as l1_pid, 6 l2.parent_id as l2_pid, 7 l3.parent_id as l3_pid, 8 l4.parent_id as l4_pid 9 from direct l1,direct l2,direct l3,direct l4 10 where l1.id = l2.parent_id(+) 11 and l2.id = l3.parent_id(+) 12 and l3.id = l4.parent_id(+) 13 order by l1.name,l2.name,l3.name,l4.name 14*L1_ID PARENT L2_ID CHILD L3_ID SUB_CHILD L4_ID SUB2_CHILD L1_PID L2_PID L3_PID L4_PID----- ------------ ----- ------------ ----- ------------- ---------- ------------ ------ ------ ------ ------ 5 ADMIN 10 catawrvw.sql 4 5 5 ADMIN 9 catcr.sql 4 5 3 BIN 1 4 RDBMS 5 ADMIN 10 catawrvw.sql 1 4 5 4 RDBMS 5 ADMIN 9 catcr.sql 1 4 5 4 RDBMS 6 demo 1 4 4 RDBMS 7 install 1 4 4 RDBMS 8 jlib 1 4 2 assistants 1 10 catawrvw.sql 5 9 catcr.sql 5 6 demo 4 7 install 4 8 jlib 4 1 oracle 3 BIN 1 1 oracle 4 RDBMS 5 ADMIN 10 catawrvw.sql 1 4 5 1 oracle 4 RDBMS 5 ADMIN 9 catcr.sql 1 4 5 1 oracle 4 RDBMS 6 demo 1 4 1 oracle 4 RDBMS 7 install 1 4 1 oracle 4 RDBMS 8 jlib 1 4 1 oracle 2 assistants 121 rows selected.

 

 SQL> select l1.id as l1_id,l1.name as parent, 2 l2.id as l2_id,l2.name as child, 3 l3.id as l3_id,l3.name as sub_child, 4 l4.id as l4_id,l4.name as sub2_child, 5 l1.parent_id as l1_pid, 6 l2.parent_id as l2_pid, 7 l3.parent_id as l3_pid, 8 l4.parent_id as l4_pid 9 from direct l1,direct l2,direct l3,direct l4 10 where l1.id = l2.parent_id(+) 11 and l2.id = l3.parent_id(+) 12 and l3.id = l4.parent_id(+) 13 and l1.id = 1 14 order by l1.name,l2.name,l3.name,l4.name 15 ;L1_ID PARENT L2_ID CHILD L3_ID SUB_CHIL L4_ID SUB2_CHILD L1_PID L2_PID L3_PID L4_PID----- ---------- ----- ----------- ----- -------- ---------- ------------ ------ ------ ------ ------ 1 oracle 3 BIN 1 1 oracle 4 RDBMS 5 ADMIN 10 catawrvw.sql 1 4 5 1 oracle 4 RDBMS 5 ADMIN 9 catcr.sql 1 4 5 1 oracle 4 RDBMS 6 demo 1 4 1 oracle 4 RDBMS 7 install 1 4 1 oracle 4 RDBMS 8 jlib 1 4 1 oracle 2 assistants 1