oracle使用connect by进行级联查询 树型菜单

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了oracle使用connect by进行级联查询 树型菜单，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含6772字，纯文字阅读大概需要10分钟。

内容图文

connect by进行级联查询 树型菜单（转）

        connect by可以用于级联查询，常用于对具有树状结构的记录查询某一节点的所有子孙节点或所有祖辈节点。 

       来看一个示例，现假设我们拥有一个菜单表t_menu，其中只有三个字段：id、name和parent_id。它们是具有父子关系的，最顶级的菜单对应的parent_id为0。现假设我们拥有如下记录：

id	name	parent_id
1	菜单01	0
2	菜单02	0
3	菜单03	0
4	菜单0101	1
5	菜单0102	1
6	菜单0103	1
7	菜单010101	4
8	菜单010201	5
9	菜单010301	6
10	菜单0201	2
11	菜单0202	2
12	菜单020101	10
13	菜单020102	10
14	菜单020103	10
15	菜单0301	3
16	菜单0302	3
17	菜单030201	16
18	菜单030202	16
19	菜单030203	16

 

       如果这个时候我们需要查询“菜单01”以及其下所有的子孙菜单应该怎么办呢？如果使用connect by的话这将会非常简单，使用如下SQL语句就可以达到对应的效果。

Sql代码  

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

  

       connect by是需要跟start with一起使用的。connect by后跟的是连接条件，在connect by后接的条件通常都需要使用关键字“prior”，可以简单的把它理解为上一级，所以上述例子中“connect by parent_id=prior id”就表示连接条件为parent_id等于上级的id，查找到下一级记录后又会找parent_id等于下一级记录的id的记录，而prior对应的最顶层的记录就是通过start with来确定的，start with后接对应的筛选条件，表示最顶层的记录是哪些，最顶层的记录可以有多个，比如我想查找“菜单01”下的子孙菜单，但是不包括“菜单01”本身，那么我就可以使用如下的SQL语句进行查找，此时“start with parent_id=1”对应的记录就会有多条。

Sql代码  

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

 

       对应的结果为：

id	name	parent_id
4	菜单0101	1
5	菜单0102	1
6	菜单0103	1
7	菜单010101	4
8	菜单010201	5
9	菜单010301	6

 

       此外，如果我们想查找“菜单010101”对应的祖辈菜单也非常简单，如下SQL就可以实现该功能，即从“菜单010101”的父菜单（对应id为4）开始查找。

Sql代码  

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

 

       对应的结果为：

id	name	parent_id
1	菜单01	0
4	菜单0101	1

 

level

       使用connect by时我们可以使用内置的类似于rownum的一个叫level的伪列，该列表示当前记录相对于start with记录的一个层级，start with记录的level为1。如上面的两条SQL语句，如果加上level的话对应的结果将是这样的。

Sql代码  

1. select level,t.* from t_menu t connect by parent_id=prior id start with parent_id=1;  

 

       对应的结果为：

level	id	name	parent_id
1	4	菜单0101	1
1	5	菜单0102	1
1	6	菜单0103	1
2	7	菜单010101	4
2	8	菜单010201	5
2	9	菜单010301	6

 

Sql代码  

1. select level,t.* from t_menu t connect by id=prior parent_id start with id=4;  

 

       对应的结果为：

level	id	name	parent_id
2	1	菜单01	0
1	4	菜单0101	1

 

       有了level后，我们就可以对查询的level做一个限制，比如只查从最顶层开始向下两级的菜单。

Sql代码  

1. select level,t.* from t_menu t where level<3 connect by prior id= parent_id start with parent_id=0;  

  

       从上述SQL我们可以看到where条件是直接跟在from之后的，使用connect by时我们的where条件不是在connect by之前对数据进行过滤的，而是在connect by之后才对所有的数据进行过滤的，这一点跟使用分组语句group by时是不一样的，group by是先通过where对需要分组的数据进行过滤后再通过group by来分组的。

 

nocycle和connect_by_iscycle

       如果我们的记录中存在循环的父子关系，则使用connect by进行查询时会抛出异常，如A->B、B->C、C->A这样的记录。解决办法是在connect by语句后加上“nocycle”，表示不循环查询，如：

Sql代码  

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

  

       使用nocycle后对于A->B、B->C、C->A这样的记录会通过查询B，然后通过B查询C，再通过C查询A时发现已经循环了，就不再查询了，即在C这条记录这里循环了。在对存在循环记录的查询中我们也可以通过“connect_by_iscycle”找到是哪一条记录循环了，“connect_by_iscycle”也是一个伪列，其必须和nocycle一起使用。伪列“connect_by_iscycle”对应的值有0和1，如果某一条记录的connect_by_iscycle对应的值为1则表示从该条记录这里开始循环了。如下是一个使用connect_by_iscycle的示例。

Sql代码  

1. select connect_by_iscycle,t.* from t_menu t connect by nocycle prior id=parent_id start with parent_id=0;  

  

connect_by_isleaf

       connect_by_isleaf也是一个伪列，其表示对应的记录是否是一个叶子节点，即在进行connect by时不能通过该记录找到下一条记录。其对应的值有0和1,0表示非叶子节点，1表示是叶子节点。如我只想找出是叶子节点的菜单时对应的SQL可以这样写：

Sql代码  

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

  

connect_by_root

       connect_by_root表示根节点，即某一条记录所对应的最顶级的记录，其用法跟prior类似，后面也需要跟一个字段名。如下面示例可以查询所有叶子节点菜单的最顶级菜单和上级菜单的名称。

Sql代码  

1. select connect_by_root name as root_name, prior name as prior_name,t.* from t_menu t where connect_by_isleaf=1 connect by prior id=parent_id start with parent_id=0;  

  

       对应上表的记录，在上述SQL中查询出来的结果应该如下所示：

root_name	prior_name	id	name	parent_id
菜单01	菜单0101	7	菜单010101	4
菜单01	菜单0102	8	菜单010201	5
菜单01	菜单0103	9	菜单010301	6
菜单02	菜单02	11	菜单0202	2
菜单02	菜单0201	12	菜单020101	10
菜单02	菜单0201	13	菜单020102	10
菜单02	菜单0201	14	菜单020103	10
菜单03	菜单03	15	菜单0301	3
菜单03	菜单0302	17	菜单030201	16
菜单03	菜单0302	18	菜单030202	16
菜单03	菜单0302	19	菜单030203	16

 

sys_connect_by_path

       sys_connect_by_path(column,delimiter)可以用来展示以指定column和分隔符delimiter表示从根节点到当前节点的路径。以下SQL用来查询id为2的菜单下叶子节点的信息，包括以字段name和分隔符“>”表示的其对应的根节点的路径。

Sql代码  

1. select sys_connect_by_path(name, ‘>‘) as connect_path,t.* from t_menu t where connect_by_isleaf=1 connect by prior id=parent_id start with id=2;  

 

       对应结果如下所示：

connect_path	id	name	parent_id
>菜单02>菜单0202	11	菜单0202	2
>菜单02>菜单0202>菜单020101	12	菜单020101	10
>菜单02>菜单0202>菜单020102	13	菜单020102	10
>菜单02>菜单0202>菜单020103	14	菜单020103	10

 

排序order

       可以使用order by对connect by之后的结果进行排序，此时order by需放在最末端，而不像where筛选那样直接定义在from之后。如需对connect by之后的结果按id进行排序，则可以使用如下SQL语句：

Sql代码  

1. select t.* from t_menu t connect by parent_id=prior id start with parent_id=0 order by id;  

  

       除了传统的针对查询结果的排序外，connect by语句还支持对同一父节点下的子节点进行排序，这是通过order siblings by来定义的。如我们需要查询id为2的菜单下的所有子孙菜单，然后对具有同一父节点的菜单按id进行倒序排列，则我们的SQL语句可以如下定义：

Sql代码  

1. select t.* from t_menu t connect by parent_id=prior id start with id=2 order siblings by id desc;  

  

       对应的结果会是这样子：

id	name	parent_id
2	菜单02	0
11	菜单0202	2
10	菜单0201	2
14	菜单020103	10
13	菜单020102	10
12	菜单020101	10

 

       如上表所示，我们可以看到“菜单0201”和“菜单0202”具有相同的父节点“菜单02”，它们按照id进行倒序排列，所有“菜单0202”在“菜单0201”之前，同样“菜单020101”、“菜单020102”和“菜单020103”具有相同的父节点“菜单0201”，所以它们也是按照id的倒序排列。

   

一次针对connect by的查询优化

       有这么一个需求：表A表示分类，表B表示任务模板，A< 本文系统来源：http://www.cnblogs.com/ut2016-progam/p/6417773.html

内容总结

以上是互联网集市为您收集整理的oracle使用connect by进行级联查询 树型菜单全部内容，希望文章能够帮你解决oracle使用connect by进行级联查询 树型菜单所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

---