1、建表、插入测试数据
create table t_menu(id int,name varchar2(50),parentId int);
2、测试查询
(1)查询 以id=1为起始值 及其下所有子孙;
select * from t_menu connect by parent_id=prior id start with id=1;
(2)查询以id=1其下所有子孙,但不含有其本身,可以看到比上面查询语句少一条记录。
select * from t_menu t connect by t.parentid = prior id start with t.id = 1
(3)查询id=9的父亲祖辈
select * from t_menu t connect by t.id = prior t.parentid start with t.id = '9'
(4)查询时添加层级level,方便前端展示树菜单时做判断使用。
select level,t.* from t_menu t connect by t.parentid = prior id start with t.id = 1
(5)查询 添加叶子节点列connect_by_isleaf
select connect_by_isleaf,level,t.* from t_menu t connect by t.parentid = prior id start with t.id = 1
上图可以看出 level=3的都是最后一级,就是叶子节点,0表示非叶子节点,1表示是叶子节点。
(6)展示层级关系 sys_connect_by_path
select sys_connect_by_path(name, '>') as connect_path,t.*
from t_menu t connect by prior id = t.parentid start with t.id = 1