如果你的表结构类似这样的:id,parent_id,如果要查出所有的叶子节点,也就是不包含在parent_id中的id,所以查所有叶子节点很简单: select id from tablename where id not in (select parent_id from tablename);要查询指定节点下的所有叶子节点,试试下面的方法SQL> select * from b; C1 C2 ---------- ---------- 1 2 1 3 2 4 3 5 2 6 5 7 67 rows selectedSQL> select c1,c2 from 2 (select b.*,row_number() over(partition by rownum - level order by level desc) rn 3 from b start with c1 =2 connect by prior c1 = c2 4 ) where rn = 1 5 / C1 C2 ---------- ---------- 4 3 7 6
能详细解释一下吗?rownum - level看不明白
如果表中只有一棵树,可以 select * from tablename where parent_id not in(select id from tablename)
select * from tablename where c1 not in(select c2 from tablename)row_number() 是分组(partition by )之后 该条记录在本组内的序号,而 rownum是一个查询结果的流水序号。 level是进行递归查询(connect by start with)后的层次。
select id from tablename where id not in (select parent_id from tablename);要查询指定节点下的所有叶子节点,试试下面的方法SQL> select * from b; C1 C2
---------- ----------
1
2 1
3 2
4 3
5 2
6 5
7 67 rows selectedSQL> select c1,c2 from
2 (select b.*,row_number() over(partition by rownum - level order by level desc) rn
3 from b start with c1 =2 connect by prior c1 = c2
4 ) where rn = 1
5 / C1 C2
---------- ----------
4 3
7 6
select * from tablename where parent_id not in(select id from tablename)
level是进行递归查询(connect by start with)后的层次。
http://blog.csdn.net/precipitant/archive/2005/08/05/446797.aspx
2 from b start with c1 =2 connect by prior c1 = c2
3 / C1 C2 LEVEL ROWNUM ROWNUM-LEVEL
---------- ---------- ---------- ---------- ------------
2 1 1 1 0
3 2 2 2 0
4 3 3 3 0
5 2 2 4 2 ---这里是树的分支
6 5 3 5 2
7 6 4 6 26 rows selected