level oracle 字段名_Oracle 树查询 level的用法

首先创建

一张表menu记录菜单的层级情况。

表结构如下：

menu_id number,

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 rpad(' ',(level-1)*3)||menu_name from menu

connect by parent_id = prior menu_id

start with parent_id is null

connect by 子句定义表中的各个黄是如何相互联系的

start with 子句定义数据黄查询的初始起点

level 表示查询深度