select tt.parentid from ( select rownum rn,id,parentid from t1 start with id=10 connect by prior parentid=id order by parent_id ) tt where rn=2
select * FROM table_name start with parent_id=1 connect by PRIOR id=parent_id
create table t1 as ( (select 1 id, -1 parentid, 0 detail from dual) union (select 101 id, 1 parentid, 0 detail from dual) union (select 10101 id, 101 parentid, 0 detail from dual) union (select 1010101 id, 10101 parentid, 1 detail from dual) union (select 102 id, 1 parentid, 0 detail from dual) union (select 10201 id, 102 parentid, 1 detail from dual) ) /SQL> select * from t1 ID PARENTID DETAIL --------- --------- --------- 1 -1 0 101 1 0 102 1 0 10101 101 0 10201 102 1 1010101 10101 16 rows selected.SQL> select id,parentid,detail,LEVEL 2 from t1 3 start with id=10101 4 connect by prior parentid=id 5 / ID PARENTID DETAIL LEVEL ---------- ---------- ---------- ---------- 10101 101 0 1 101 1 0 2 1 -1 0 3 SQL> 1 select * from ( 2 select rownum rn,id,parentid,detail,LEVEL 3 from t1 4 start with id=10101 5 connect by prior parentid=id order by parentid ) tt 6* where rn=2 / RN ID PARENTID DETAIL LEVEL ---------- ---------- ---------- ---------- ---------- 2 101 1 0 2
--测试数据 create table table1( cid int,cparid int ); insert into table1 select 1,0 from dual union all select 2,1 from dual union all select 3,1 from dual union all select 4,2 from dual union all select 5,4 from dual union all select 6,0 from dual union all select 7,6 from dual union all select 8,6 from dual; --执行查询 --测试数据 create table table1( cid int,cparid int ); insert into table1 select 1,0 from dual union all select 2,1 from dual union all select 3,1 from dual union all select 4,2 from dual union all select 5,4 from dual union all select 6,0 from dual union all select 7,6 from dual union all select 8,6 from dual; --执行查询 select distinct (select count(*) from table1 start with cid=t.cparid connect by prior cid=cparid) sum1,t.cparid from table1 t order by sum1 desc --查询结果 5 1
create table table1( cid int,cparid int ); insert into table1 select 1,0 from dual union all select 2,1 from dual union all select 3,1 from dual union all select 4,2 from dual union all select 5,4 from dual union all select 6,0 from dual union all select 7,6 from dual union all select 8,6 from dual;就是这样的一张表,其中 5的上一级是4,4的上一级是2,2的上一级是1,以为1的上级是0所以1是根节点,我要查询 5 得出应该是 2
select rownum rn,id,parentid from t1
start with id=10
connect by prior parentid=id
order by parent_id ) tt
where rn=2
start with parent_id=1
connect by PRIOR id=parent_id
(
(select 1 id, -1 parentid, 0 detail from dual)
union
(select 101 id, 1 parentid, 0 detail from dual)
union
(select 10101 id, 101 parentid, 0 detail from dual)
union
(select 1010101 id, 10101 parentid, 1 detail from dual)
union
(select 102 id, 1 parentid, 0 detail from dual)
union
(select 10201 id, 102 parentid, 1 detail from dual)
)
/SQL> select * from t1 ID PARENTID DETAIL
--------- --------- ---------
1 -1 0
101 1 0
102 1 0
10101 101 0
10201 102 1
1010101 10101 16 rows selected.SQL> select id,parentid,detail,LEVEL
2 from t1
3 start with id=10101
4 connect by prior parentid=id
5 / ID PARENTID DETAIL LEVEL
---------- ---------- ---------- ----------
10101 101 0 1
101 1 0 2
1 -1 0 3 SQL> 1 select * from (
2 select rownum rn,id,parentid,detail,LEVEL
3 from t1
4 start with id=10101
5 connect by prior parentid=id order by parentid ) tt
6* where rn=2 / RN ID PARENTID DETAIL LEVEL
---------- ---------- ---------- ---------- ----------
2 101 1 0 2
create table table1( cid int,cparid int );
insert into table1
select 1,0 from dual union all
select 2,1 from dual union all
select 3,1 from dual union all
select 4,2 from dual union all
select 5,4 from dual union all
select 6,0 from dual union all
select 7,6 from dual union all
select 8,6 from dual;
--执行查询
--测试数据
create table table1( cid int,cparid int );
insert into table1
select 1,0 from dual union all
select 2,1 from dual union all
select 3,1 from dual union all
select 4,2 from dual union all
select 5,4 from dual union all
select 6,0 from dual union all
select 7,6 from dual union all
select 8,6 from dual;
--执行查询
select
distinct (select count(*) from table1
start with cid=t.cparid connect by prior cid=cparid) sum1,t.cparid
from table1 t
order by sum1 desc
--查询结果
5 1
insert into table1
select 1,0 from dual union all
select 2,1 from dual union all
select 3,1 from dual union all
select 4,2 from dual union all
select 5,4 from dual union all
select 6,0 from dual union all
select 7,6 from dual union all
select 8,6 from dual;就是这样的一张表,其中 5的上一级是4,4的上一级是2,2的上一级是1,以为1的上级是0所以1是根节点,我要查询 5 得出应该是 2