17,377
社区成员
发帖
与我相关
我的任务
分享
select id,name,parentid
from 你的表名
where connect_by_isleaf=1
start with parentid = 2
connect by prior parentid=id;
SELECT id, NAME, parentid
FROM table_name
WHERE connect_by_isleaf = 1
START WITH NAME = '张三儿子儿子'
CONNECT BY PRIOR parentid = ID;
SQL> with temp as
2 (select 1 id, '张三' name, 0 parentid
3 from dual
4 union all
5 select 2 id, '张三儿子' name, 1 parentid
6 from dual
7 union all
8 select 3 id, '张三儿子儿子' name, 2 parentid from dual)
9 select id, name, parentid
10 from (select id, name, parentid, CONNECT_BY_isleaf leaf
11 from temp
12 start with name = '张三儿子儿子'
13 connect by prior parentid = id)
14 where leaf = 1
15 /
ID NAME PARENTID
---------- ------------ ----------
1 张三 0
SQL>
SQL> with temp as(
2 select 1 id,'张三' name,0 parentid from dual
3 union all
4 select 2 id,'张三儿子' name,1 parentid from dual
5 union all
6 select 3 id,'张三儿子儿子' name,2 parentid from dual
7 )
8 select id,name,parentid from temp
9 start with parentid = 0
10 connect by prior parentid = id
11 /
ID NAME PARENTID
---------- ------------ ----------
1 张三 0
SQL>
with temp as(
select 1 id,'张三' name,0 parentid from dual
union all
select 2 id,'张三儿子' name,1 parentid from dual
union all
select 3 id,'张三儿子儿子' name,2 parentid from dual
)
select id,name,parentid from temp
start with parentid = 0
connect by parentid = id
select id,name,parentid,
lag(parentid,1)over(order by id)
as last_parent
from table_name ;