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

表结构以及所含数据行 SQL> desc direct Name Null? Type ----------------------------------------- -------- ---------------------------- ID NUMBER NAME VARCHAR2(20) PARENT_ID NUMBER table--L1 SQL> select * from direct; ID NAME PARENT_ID ---------- -------------------- ---------- 1 oracle 2 assistants 1 3 BIN 1 4 RDBMS 1 5 ADMIN 4 6 demo 4 7 install 4 8 jlib 4 9 catcr.sql 5 10 catawrvw.sql 5 相同的table不同显示方式因为后面要做等值连接 table--L2 PARENT_ID ID NAME ---------- ---------- -------------------- 1 oracle 1 2 assistants 1 3 BIN 1 4 RDBMS 4 5 ADMIN 4 6 demo 4 7 install 4 8 jlib 5 9 catcr.sql 5 10 catawrvw.sql 10 rows selected.

 

实际过程很简单 l1 ，l2的关系就是如上图所示的l1里面的id键值作为l2中parent_id的字面值来搜索记录。

比如第一条l1记录里面是ID = 1 PARENT_ID=NULL, 去用这个值赋给 L2中parent_id = 1 这样l2里面有三条记录

都是满足这个条件的，所以这三条记录里面的name就是l1的子节点

 

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 L1_ID PARENT L2_ID CHILD L1_PID L2_PID ---------- ---------- ---------- --------------- ---------- ---------- 1 oracle 4 RDBMS 1 1 oracle 3 BIN 1 1 oracle 2 assistants 1 4 RDBMS 8 jlib 1 4 4 RDBMS 7 install 1 4 4 RDBMS 6 demo 1 4 4 RDBMS 5 ADMIN 1 4 5 ADMIN 10 catawrvw.sql 4 5 5 ADMIN 9 catcr.sql 4 5 9 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 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 8 jlib 1 4 1 oracle 4 RDBMS 7 install 1 4 1 oracle 4 RDBMS 6 demo 1 4 1 oracle 4 RDBMS 5 ADMIN 1 4 4 RDBMS 5 ADMIN 10 catawrvw.sql 1 4 5 4 RDBMS 5 ADMIN 9 catcr.sql 1 4 5 6 rows selected.

14 8第一条记录用8作为Parent 去最初l1表去检索没有

后来发现l3_id列中只有parent=5还有有子记录，所以只有两条了

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 ; L1_ID PARENT L2_ID CHILD L3_ID SUB_CHILD L4_ID SUB2_CHILD L1_PID L2_PID L3_PID L4_PID ----- ---------- ----- ---------- ----- -------------- ---------- -------------------- ------ ------ ------ ---------- 1 oracle 4 RDBMS 5 ADMIN 10 catawrvw.sql 1 4 5 1 oracle 4 RDBMS 5 ADMIN 9 catcr.sql 1 4 5