Oracle SQL查询-树操作

一、Why：为什么

项目里遇到了查询一个单表里，某个父ID下的所有子ID记录，多层的树形结构；

二、How：怎么查询

1）查找所有顶级“父菜单”节点

select * from t_menu where parent_id = 0;

2) 查找“父菜单1”的所有“一级菜单”（不需要树查询）

select * from t_menu where parent_id = 1;

3) 查找“父菜单1”的所有子菜单，包括：“一级菜单”、”二级菜单“、”三级菜单“ (树查询)

select * from t_menu start with id=1 connect by prior id = parent_id;

4）查找“一级菜单6”的直属“父菜单” （不需要树查询）

select * from t_menu where id = (select parent_id from t_menu where id = 6);

或者

select a.* from t_menu a inner join t_menu b on a.id = b.parent_id and b.id = 6; 

5）查找“三级菜单40”的所有父菜单，包括：“二级菜单”、”一级菜单“、“父菜单” （树查询）

select * from t_menu start with id = 40 connect by prior parent_id = id;

PS：3）和5）的区别主要是 prior关键字的位置，查子节点和查父节点；

start_with 指定当前根节点

在3）中 prior id = parent_id，以当前根节点的id 为始，迭代（子节点也根据该规则查找）找出所有parent_id = 当前id的所有记录；

在5）中 prior parent_id = id，以当前根节点的parent_id为始，迭代找出所有id = 当前parent_id的所有记录；

总之：connect by 指定条件，prior 指定当前记录的条件；

三、What：查什么

做一些查询，理解语法

1）查询”父菜单1“所在的其他“父菜单” （不需要树查询）

select a.* from t_menu a inner join t_menu b on a.parent_id = b.parent_id and b.id =1;

select * from t_menu where parent_id = (select parent_id from t_menu where id = 1);

2）查找“三级菜单40”的所有其他“三级菜单”（树查询）

with tmp as (
select id,title,parent_id,level lev from t_menu start with parent_id = 0 connect by prior id = parent_id
)
select * from tmp where lev = (select lev from tmp where id = 40);

3）查找“三级菜单40”的父节点“二级菜单”的所有同级节点

with tmp as (
select id,title,parent_id,level lev from t_menu start with parent_id = 0 connect by prior id = parent_id
)
select id,title,parent_id,lev from tmp where lev = (
select lev -1 from tmp where id = 40
)

4）列出“三级菜单40” 的title全部路径

sys_connect_by_path函数：列出迭代的属性路径；

从根节点出发（/父菜单1/一级菜单7/二级菜单23/三级菜单40）：

select sys_connect_by_path(title, '/') from t_menu
where id = 40
start with parent_id =0
connect by parent_id = prior id;

从当前节点出发：

select sys_connect_by_path(title, '/') from t_menu
where id = 40
start with parent_id =0
connect by parent_id = prior id;

5）列出“三级菜单40”的 根节点信息

connect_by_root 函数用在列之前，记录当前根节点的信息；

select connect_by_root title,t.*
from t_menu t
start with id=40
connect by prior parent_id = id;

6）判断“二级菜单30”是否是子节点

connect_by_isleaf函数判断当前节点是否为叶子节点；

select connect_by_isleaf,t.* from t_menu t 
where t.id =30
start with id=30 connect by prior parent_id = id;

 

附录：

1. 准备数据

1）表

create table t_menu(
   id number(10) not null, --主键id
   title varchar2(50), --标题
   parent_id number(10) --父id
)

2）测试数据

--父菜单
insert into t_menu(id, title, parent_id) values(1, '父菜单1',0);
insert into t_menu(id, title, parent_id) values(2, '父菜单2',0);
insert into t_menu(id, title, parent_id) values(3, '父菜单3',0);
insert into t_menu(id, title, parent_id) values(4, '父菜单4',0);
insert into t_menu(id, title, parent_id) values(5, '父菜单5',0);
--一级菜单
insert into t_menu(id, title, parent_id) values(6, '一级菜单6',1);
insert into t_menu(id, title, parent_id) values(7, '一级菜单7',1);
insert into t_menu(id, title, parent_id) values(8, '一级菜单8',1);
insert into t_menu(id, title, parent_id) values(9, '一级菜单9',2);
insert into t_menu(id, title, parent_id) values(10, '一级菜单10',2);
insert into t_menu(id, title, parent_id) values(11, '一级菜单11',2);
insert into t_menu(id, title, parent_id) values(12, '一级菜单12',3);
insert into t_menu(id, title, parent_id) values(13, '一级菜单13',3);
insert into t_menu(id, title, parent_id) values(14, '一级菜单14',3);
insert into t_menu(id, title, parent_id) values(15, '一级菜单15',4);
insert into t_menu(id, title, parent_id) values(16, '一级菜单16',4);
insert into t_menu(id, title, parent_id) values(17, '一级菜单17',4);
insert into t_menu(id, title, parent_id) values(18, '一级菜单18',5);
insert into t_menu(id, title, parent_id) values(19, '一级菜单19',5);
insert into t_menu(id, title, parent_id) values(20, '一级菜单20',5);
--二级菜单
insert into t_menu(id, title, parent_id) values(21, '二级菜单21',6);
insert into t_menu(id, title, parent_id) values(22, '二级菜单22',6);
insert into t_menu(id, title, parent_id) values(23, '二级菜单23',7);
insert into t_menu(id, title, parent_id) values(24, '二级菜单24',7);
insert into t_menu(id, title, parent_id) values(25, '二级菜单25',8);
insert into t_menu(id, title, parent_id) values(26, '二级菜单26',9);
insert into t_menu(id, title, parent_id) values(27, '二级菜单27',10);
insert into t_menu(id, title, parent_id) values(28, '二级菜单28',11);
insert into t_menu(id, title, parent_id) values(29, '二级菜单29',12);
insert into t_menu(id, title, parent_id) values(30, '二级菜单30',13);
insert into t_menu(id, title, parent_id) values(31, '二级菜单31',14);
insert into t_menu(id, title, parent_id) values(32, '二级菜单32',15);
insert into t_menu(id, title, parent_id) values(33, '二级菜单33',16);
insert into t_menu(id, title, parent_id) values(34, '二级菜单34',17);
insert into t_menu(id, title, parent_id) values(35, '二级菜单35',18);
insert into t_menu(id, title, parent_id) values(36, '二级菜单36',19);
insert into t_menu(id, title, parent_id) values(37, '二级菜单37',20);
--三级菜单
insert into t_menu(id, title, parent_id) values(38, '三级菜单38',21);
insert into t_menu(id, title, parent_id) values(39, '三级菜单39',22);
insert into t_menu(id, title, parent_id) values(40, '三级菜单40',23);
insert into t_menu(id, title, parent_id) values(41, '三级菜单41',24);
insert into t_menu(id, title, parent_id) values(42, '三级菜单42',25);
insert into t_menu(id, title, parent_id) values(43, '三级菜单43',26);
insert into t_menu(id, title, parent_id) values(44, '三级菜单44',27);
insert into t_menu(id, title, parent_id) values(45, '三级菜单45',28);
insert into t_menu(id, title, parent_id) values(46, '三级菜单46',28);
insert into t_menu(id, title, parent_id) values(47, '三级菜单47',29);
insert into t_menu(id, title, parent_id) values(48, '三级菜单48',30);
insert into t_menu(id, title, parent_id) values(49, '三级菜单49',31);
insert into t_menu(id, title, parent_id) values(50, '三级菜单50',31);

commit;