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
表示查询深度
===================================