oracle 树查询 level用法

1.       创建表 menu

create table MENU

(

 MENU_ID   NUMBER not null,

 PARENT_ID NUMBER,

 MENU_NAME NVARCHAR2(20)

)

2.       插入数据

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;

4b62e4a906de97549b496

3.       查询语句

select menu_id,rpad(‘ ‘,(level-1)*4)||menu_name from menu
connect by parent_id = prior menu_id
start with parent_id is null

4b62e4a944b1e94f13ec0

显示出树的级别查询

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

4b62e4a92aef31d10447a

 

评论已经关闭。