使用connect by进行级联查询

使用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语句就可以达到对应的效果。

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”对应的记录就会有多条。

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）开始查找。

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的话对应的结果将是这样的。

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

 

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做一个限制，比如只查从最顶层开始向下两级的菜单。

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”，表示不循环查询，如：

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的示例。

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可以这样写：

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类似，后面也需要跟一个字段名。如下面示例可以查询所有叶子节点菜单的最*菜单和上级菜单的名称。

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和分隔符“>”表示的其对应的根节点的路径。

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语句：

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语句可以如下定义：

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与B是一对多的关系，每一个任务模板都属于一个特定的分类，在表B中用字段a表示所属的分类。分类存在父子关系，子分类的parent_id对应父分类的id。现假设需要统计id为1的分类及其子分类下存在的任务模板数量。对应SQL如下：

select count(1) from B b,(select id from A connect by prior id=parent_id start with id=1) a where a.id=b.a;

 

       现假设拥有另外一个表C，其表示任务实例，一个任务模板B可以拥有n个任务实例B，即B跟C之间是一对多的关系。任务实例C通过字段b关联任务模板B，另外任务实例C拥有一个字段status表示任务实例的具体状态。现假设需要统计id为1的分类及其子分类下各状态的任务实例数量。对应SQL如下：

select c.status,count(1) from B b,(select id from A connect by prior id=parent_id start with id=1) a, C c where a.id=b.a and b.id=c.b group by c.status;

 

       在A表数据量1000，B表数据量20000，C表数据量5000，id为1的分类下属的子孙分类数量为100的情况下第一条SQL的查询速度可以在0.1秒左右完成，而第二条SQL需要将近10秒才能完成。把查询id为1的分类下子孙分类的id的SQL语句“selectidfrom A connectbypriorid=parent_id startwithid=1”单独查询的速度也可以在0.1秒内完成。通常对于这种数量级别的三表查询都是可以在0.1秒内完成的，为此心想第二条SQL应该是受了子查询中connect by的影响。后来决定把分类的子查询直接作为B的in条件进行查询，如下所示：

select c.status,count(1) from B b, C c where b.a in(select id from A connect by prior id=parent_id start with id=1) and b.id=c.b group by c.status;

 

       其查询效果是一样的，心想应该还是connect by影响到了，既然单独使用connect by查询id为1的分类的子孙分类的id只需要不到0.1秒，那何不在程序里面先将id为1的分类的子孙分类id查询出来，再作为B、C联合查询的in条件，如：

select c.status,count(1) from B b, C c where b.a in(...) and b.id=c.b group by c.status;

 

       结果查询结果也可以在0.1秒内完成。