Oracle有关树的查询

1，查询所有节点

select * from busi_jgrs  start with id = '1' connect by prior id = pid;

start with id = '1'

意思是以id为1开始。

2，查询末节点

select *
  from busi_jgrs
 where id in (select id
                from (select id, CONNECT_BY_ISLEAF isleaf
                        from busi_jgrs
                       start with id = '1'
                      connect by prior id = pid) tt
               where tt.isleaf = 1)

3，查询某等级的节点。（比如等级为3）

select t.*, level, CONNECT_BY_ISLEAF isleaf
  from tree t
 where level = 3
 start with tid = '1'
connect by prior tid = pid

4，查询：某等级且为底层节点。（比如等级为2）

select t.*, level, CONNECT_BY_ISLEAF isleaf
  from tree t
 where level = 2
   and CONNECT_BY_ISLEAF = 1
 start with tid = '1'
connect by prior tid = pid;

level是Oracle本有的字段类似于rowid