使用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秒内完成。
相关推荐
`START WITH`语句指定了查询的起始节点,而`CONNECT BY`则定义了父子节点之间的关系。这种查询能够递归地获取所有子节点,对于构建层级结构的输出非常有用。 接下来,我们讨论级联删除。在Oracle中,当定义外键约束...
CONNECT BY PRIOR 当前表字段 = 级联表字段; ``` - **`START WITH`**:定义查询的起点,即树形结构的根节点。 - **`CONNECT BY PRIOR`**:用于建立记录之间的层级关系,指明如何从父节点到子节点。 #### 三、具体...
Oracle 连接查询是指使用 START WITH 和 CONNECT BY 语句来实现递归查询的方法,这种方法可以生成树形结构的数据。在 Oracle 中,START WITH 语句用于指定递归查询的开始记录,而 CONNECT BY 语句用于指定递归查询的...
Oracle 层次化查询是数据库管理系统中用于处理具有层级关系数据的一种强大工具。...通过创建临时字段和使用`START WITH`、`CONNECT BY`语句,我们可以构建一个灵活且高效的查询,方便对数据进行多级分析和操作。
级联查询或层次结构查询,如使用`CONNECT BY`和`START WITH`,允许按照层级关系检索数据,适用于组织架构或产品分类等场景。 #### 七、高级分析函数(Advanced Analytic Functions) 这些函数,如`LAG`, `LEAD`, `...
7. **其他函数**:包括位运算函数(BITAND、BITOR等)、级联查询的CONNECT_BY_ROOT,以及用于处理XML数据的XML相关的函数。 博客链接提到的iteye.com是一个知名的IT技术分享平台,博主Superivan分享的Oracle函数...
查询当前登录用户的详细信息,可以使用以下命令: ```sql -- 显示当前登录用户 SHOW USER; -- 查看系统中的所有用户 SELECT * FROM ALL_USERS; -- 连接到特定用户 CONN username/userpassword; -- 查询当前用户...
创建新用户使用`create user`命令,例如`create user ittbank identified by 123456`,并设置默认表空间。赋予用户权限,如`connect`、`resource`和`dba`,用`grant`命令,如`grant connect,resource,dba to ...
对象权限管理示例:若希望`xiaoming`用户能够查询`emp`表,则应使用`grant select on table_name to username`命令。同样,如果要撤销某用户的权限,可以使用`revoke`命令。 这些命令和操作是Oracle数据库管理的...
- **日常维护**:通常使用 `system` 用户来进行。 **2. SQL*Plus 工具使用:** - **连接(conn[ect]**:用于连接到数据库,如 `conn 用户名/密码@数据库别名 [as sysdba/syoper]`。当使用 `as sysdba` 或 `as ...
在 Oracle 中,查询表需要使用 SELECT 语句,例如: ```sql SELECT * FROM classes; ``` 这条语句查询 classes 表中的所有数据。 ### 删除表 在 Oracle 中,删除表需要使用 DROP TABLE 语句,例如: ```sql DROP ...
在进行用户创建之前,需要确保已经启动了 Oracle 监听器服务。可以通过以下命令来启动监听器: ```sql lsnrctl start ``` 接着,使用系统管理员账户登录 SQL*Plus 工具,例如使用 `sys` 账户并指定为 `SYSDBA` ...
- **子查询**:在查询语句中嵌套使用`select`语句,可以进行更复杂的逻辑处理。 #### 九、数据导出 - **完整数据库导出**:`exp userid=用户名/密码@实例名 file=文件路径 full=y log=日志文件路径`。 - **特定模式...
- `CONNECT BY` 用于创建层次查询,常用于树状结构的数据。 10. **分析函数** - `LAG()`, `LEAD()`: 访问前一行或后一行的数据。 - `RANK()`, `DENSE_RANK()`, `ROW_NUMBER()`: 在分析框架中为每一行分配唯一的...
需要注意的是,如果使用了`WITH ADMIN OPTION`授予系统权限,即使原授权用户被撤销了权限,也不会级联影响到其他已经获得相同权限的用户。 #### 三、实体权限管理 ##### 1、实体权限分类 实体权限主要包括以下几...
用户删除通常会伴随着其所有对象的删除,使用`cascade`参数可以级联删除: ```sql drop user 用户名 cascade; ``` 删除表空间时,确保无其他用户正在使用,并使用`including contents`和`including datafiles`参数...