start with connect by

最新推荐文章于 2024-04-21 22:59:02 发布

znanbeijing

最新推荐文章于 2024-04-21 22:59:02 发布

阅读量780

点赞数 1

分类专栏： PL/SQL

本文链接：https://blog.csdn.net/znanbeijing/article/details/29561653

版权

PL/SQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

1.create table test (id number,p_id number,name varchar2(10));

2.insert into  test values (5,0,'zhangda');
   ```````
   commit;

3.select * from test;

        ID       P_ID NAME
---------- ---------- ----------
         1          5 zhangsan
         2          5 zhangsi
         3          1 zxiaosan
         4          3 zxxsan
         5          0 zhangda

4.What I want to get?

col name format a15
--All
select lpad(' ',level*2,' ')||name name,id, p_id
from test t
start with t.P_ID=0
connect by prior t.id=t.p_id;

NAME                   ID       P_ID
---------------      ----------   ----------
zhangda               5          0
    zhangsan             1          5
      zxiaosan             3          1
        zxxsan               4         3
    zhangsi             2           5

--The zhangsan' children
select lpad(' ',level*2,' ')||name name,id, p_id
from test t
start with t.ID=1
connect by prior t.id=t.p_id;

NAME                   ID       P_ID
--------------- ---------- ----------
zhangsan               1          5
    zxiaosan               3          1
      zxxsan           4          3