树查询
使用树查询的前提条件是: 在一条记录中记录了当前节点的ID和这个节点的父ID。
注意:一旦数据中出现了循环记录,如两个节点互为对方的父结点,系统就会报
第一步:创建表
create table MENU
(
MENU_ID NUMBER not null,
PARENT_ID NUMBER,
MENU_NAME NVARCHAR2(20)
);
第二步:插入数据
insert into MENU (MENU_ID, PARENT_ID, MENU_NAME) values (1, null,'AAAA');
insert into MENU (MENU_ID, PARENT_ID, MENU_NAME) values (2, 1, 'BBBB');
insert into MENU (MENU_ID, PARENT_ID, MENU_NAME) values (3, 1, 'CCCC');
insert into MENU (MENU_ID, PARENT_ID, MENU_NAME) values (4, 1, 'DDDD');
insert into MENU (MENU_ID, PARENT_ID, MENU_NAME) values (5, 2, 'EEEE');
insert into MENU (MENU_ID, PARENT_ID, MENU_NAME) values (6, 2, 'FFFF');
insert into MENU (MENU_ID, PARENT_ID, MENU_NAME) values (7, 2, 'GGGG');
insert into MENU (MENU_ID, PARENT_ID, MENU_NAME) values (8, 3, 'HHHH');
commit;
第三步:查询
select menu_id,rpad(' ',(level-1)*4)||menu_name from menu
connect by parent_id = prior menu_id
start with parent_id is null
显示出树的级别查询
select menu_id,rpad(' ',(level-1)*4)||menu_name,level from menu
connect by parent_id = prior menu_id
start with parent_id is null;
也可以这样写:
select role_id,parent_grade_id,level from audit_role
where level > 1
connect by prior role_id = parent_grade_id
start with role_id=1
order by level;