本篇文章详细介绍了Oracle的递归查询语法，利用此语法，可以方便地实现递归的双向查询：
-- Tirle : Recursion query for TREE with "connect by/start with"
-- Author :
-- Create Date :
-- Version :
-- Last Modify :
目 录
一、测试准备
二、实现各种查询要求
三、要点总结
正 文
一、测试准备
1、先假设有如下部门结构。
1
/ \
2 3
/\ /|\
4 5 6 7 8
2、然后建立测试表和数据。
drop table t_dept_temp;
create table t_dept_temp(
DEPT_ID NUMBER(2) NOT NULL, --部门ID
PARENT_ID NUMBER(2) , --上级部门ID
DEPT_NAME VARCHAR2(10) , --部门名称
AMOUNT NUMBER(3) --人数
);
delete t_dept_temp;
insert into t_dept_temp (DEPT_ID,PARENT_ID,DEPT_NAME,AMOUNT) values (1,null,'1' ,2);
insert into t_dept_temp (DEPT_ID,PARENT_ID,DEPT_NAME,AMOUNT) values (2,1 ,'1-2' ,15);
insert into t_dept_temp (DEPT_ID,PARENT_ID,DEPT_NAME,AMOUNT) values (3,1 ,'1-3' ,8);
insert into t_dept_temp (DEPT_ID,PARENT_ID,DEPT_NAME,AMOUNT) values (4,2 ,'1-2-4',10);
insert into t_dept_temp (DEPT_ID,PARENT_ID,DEPT_NAME,AMOUNT) values (5,2 ,'1-2-5',9);
insert into t_dept_temp (DEPT_ID,PARENT_ID,DEPT_NAME,AMOUNT) values (6,3 ,'1-3-6',17);
insert into t_dept_temp (DEPT_ID,PARENT_ID,DEPT_NAME,AMOUNT) values (7,3 ,'1-3-7',5);
insert into t_dept_temp (DEPT_ID,PARENT_ID,DEPT_NAME,AMOUNT) values (8,3 ,'1-3-8',6);
commit;
SQL> select * from t_dept_temp;
DEPT_ID PARENT_ID DEPT_NAME AMOUNT
------- --------- ---------- ------
1 1 2
2 1 1-2 15
3 1 1-3 8
4 2 1-2-4 10
5 2 1-2-5 9
6 3 1-3-6 17
7 3 1-3-7 5
8 3 1-3-8 6
3、调整一下输出格式
col DEPT_ID format A10;
二、接下来实现各种查询要求
1、部门2及其所有下级部门。
SELECT LPAD(' ',2*(LEVEL - 1), ' ')||DEPT_ID AS DEPT_ID,
PARENT_ID,
DEPT_NAME,
AMOUNT
FROM t_dept_temp
CONNECT BY PARENT_ID = PRIOR DEPT_ID -- 找出所有PARENT_ID等于当前记录DEPT_ID的记录。
START WITH DEPT_ID = 2 ; -- 从部门2开始递归查询。
DEPT_ID PARENT_ID DEPT_NAME AMOUNT
---------- --------- ---------- ------
2 1 1-2 15
4 2 1-2-4 10
5 2 1-2-5 9
2、部门4及其所有上级部门
SELECT LPAD(' ',2*(LEVEL - 1), ' ')||DEPT_ID AS DEPT_ID,
PARENT_ID,
DEPT_NAME,
AMOUNT
FROM T_DEPT_TEMP
CONNECT BY PRIOR PARENT_ID = DEPT_ID -- 找出所有DEPT_ID等于当前记录PARENT_ID的记录
START WITH DEPT_ID = 4 ; -- 从部门4开始递归查询。
DEPT_ID PARENT_ID DEPT_NAME AMOUNT
---------- --------- ---------- ------
4 2 1-2-4 10
2 1 1-2 15
1 1 2
3、部门1的所有下级部门。
SELECT LPAD(' ',2*(LEVEL - 1), ' ')||DEPT_ID AS DEPT_ID,
PARENT_ID,
DEPT_NAME,
AMOUNT
FROM T_DEPT_TEMP
START WITH DEPT_ID = 1
CONNECT BY PARENT_ID = PRIOR DEPT_ID;
DEPT_ID PARENT_ID DEPT_NAME AMOUNT
---------- --------- ---------- ------
1 1 2
2 1 1-2 15
4 2 1-2-4 10
5 2 1-2-5 9
3 1 1-3 8
6 3 1-3-6 17
7 3 1-3-7 5
8 3 1-3-8 6
4、部门1及其所有下级部门，但是不包括部门3及其下级部门。（排除树枝）
SELECT LPAD(' ',2*(LEVEL - 1), ' ')||DEPT_ID AS DEPT_ID,
PARENT_ID,
DEPT_NAME,
AMOUNT
FROM T_DEPT_TEMP
START WITH DEPT_ID = 1
CONNECT BY PARENT_ID = PRIOR DEPT_ID
AND DEPT_ID <> 3 ; -- 不包括部门3及其下属部门(部门3和6、7、8都没出现)
DEPT_ID PARENT_ID DEPT_NAME AMOUNT
---------- --------- ---------- ------
1 1 2
2 1 1-2 15
4 2 1-2-4 10
5 2 1-2-5 9
5、部门1及其所有下级部门，但是仅不包括部门3。（排除节点）
SELECT LPAD(' ',2*(LEVEL - 1), ' ')||DEPT_ID AS DEPT_ID,
PARENT_ID,DEPT_NAME,AMOUNT
FROM T_DEPT_TEMP
WHERE DEPT_ID <>3 -- 仅仅不包括部门3(输出结果中，3的下级部门6、7、8还是出现了)
START WITH DEPT_ID = 1
CONNECT BY PARENT_ID = PRIOR DEPT_ID -- 执行顺序where在connect by之后
;
DEPT_ID PARENT_ID DEPT_NAME AMOUNT
---------- --------- ---------- ------
1 1 2
2 1 1-2 15
4 2 1-2-4 10
5 2 1-2-5 9
6 3 1-3-6 17
7 3 1-3-7 5
8 3 1-3-8 6
6、部门1及其所有下级部门，且所有部门按照人数升序排列。
SELECT LPAD(' ',2*(LEVEL - 1), ' ')||DEPT_ID AS DEPT_ID,
PARENT_ID,
DEPT_NAME,
AMOUNT
FROM T_DEPT_TEMP
START WITH DEPT_ID = 1
CONNECT BY PARENT_ID = PRIOR DEPT_ID
ORDER BY AMOUNT ASC ; -- 排序在最后被执行，所以DEPT_ID完全被打乱了，而且层级关系也打乱了。
-- In a hierarchical query, do not specify either ORDER BY or GROUP BY,
-- as they will destroy the hierarchical order of the CONNECT BY results.
DEPT_ID PARENT_ID DEPT_NAME AMOUNT
---------- --------- ---------- ------
1 1 2
7 3 1-3-7 5
8 3 1-3-8 6
3 1 1-3 8
5 2 1-2-5 9
4 2 1-2-4 10
2 1 1-2 15
6 3 1-3-6 17
7、部门1及其所有下级部门，每个部门的下一级部门之间，按照人数降序排列。(有同一上级的那些部门？？？
-- If you want to order rows of siblings of the same parent,
-- then use the ORDER SIBLINGS BY clause.
SELECT LPAD(' ',2*(LEVEL - 1), ' ')||DEPT_ID AS DEPT_ID,
PARENT_ID,
DEPT_NAME,
AMOUNT
FROM T_DEPT_TEMP
START WITH DEPT_ID = 1
CONNECT BY PARENT_ID = PRIOR DEPT_ID
ORDER SIBLINGS BY AMOUNT ASC; -- 同属部门间排序
-- 输出结果可见，部门3、2作为一组进行排序，部门7、8、6一组，5、4一组。
DEPT_ID PARENT_ID DEPT_NAME AMOUNT
---------- --------- ---------- ------
1 1 2
3 1 1-3 8
7 3 1-3-7 5
8 3 1-3-8 6
6 3 1-3-6 17
2 1 1-2 15
5 2 1-2-5 9
4 2 1-2-4 10
三、要点总结
1、子句的语法书写顺序。
select -> from -> where -> start with -> connect by -> order by
where写在connect by后面就不行，报错。
2、子句的执行顺序
from -> start with -> connect by -> where -> select -> order by
执行顺序where在connect by之后，可以从例5证明。
可是书写SQL语句的时候，却只能写前面，注意理解。
3、如何理解和记忆“CONNECT BY PRIOR PARENT_ID = DEPT_ID ”的含义呢？
现在看这个例子似乎很直观，但是今后实际应用时，条件变化后，如何推断查询结果呢？
这里我自己总结一种方法，前提是要理解SQL语句执行时，是一条一条记录来处理的。
每条满足START WITH语句条件的记录被依次取出，暂且把每次被取出处理的记录，称为当前记录。
“PRIOR PARENT_ID”表明从当前记录得到PARENT_ID，
然后" = DEPT_ID"说明找到表中所有DEPT_ID等于当前记录PARENT_ID的记录，也就是找当前记录PARENT_ID所指向的记录。
因为PARENT_ID的取值含义是上级节点，所以说明是向树的根节点方向的搜索。（我的上级是谁？）
反之，如果是“CONNECT BY PARENT_ID = PRIOR DEPT_ID”，“PRIOR”在DEPT_ID一边，就是找所有PARENT_ID等于当前记录DEPT_ID的记录，是向树的叶子方向的搜索。（谁的上级是我？）
找到结果记录集以后，从第一条记录开始递归处理，依此类推。
4、前序遍历
由于是递归处理，从例3可以看出，树的根节点向叶子节点递归查询时，查询节点的顺序是按照树的前序遍历进行的。
5、排序
例6和例7说明了两种排序的区别。
In a hierarchical query, do not specify either ORDER BY or GROUP BY, as they will destroy the hierarchical order of the CONNECT BY results. If you want to order rows of siblings of the same parent, then use the ORDER SIBLINGS BY clause. See order_by_clause.
6、伪列LEVEL
只能随CONNECT BY子句一起使用，是一个整数，代表递归的层次深度。也就是节点在树中所处深度。
根节点时等于1，根节点的叶子节点的深度等于2，依此类推。
LPAD(' ',2*(LEVEL - 1), ' ')||DEPT_ID 正是利用了LEVEL来为每个层级的字段提供不同的缩进。
over
分享到：
相关推荐
### SQL语句递归：Oracle中的递归查询详解 在数据库查询语言中，递归查询是一种非常强大的功能，尤其在处理具有层次结构的数据时尤为重要。本文将深入探讨Oracle数据库中的递归查询，包括其基本语法、工作原理以及...
#### 二、SQL递归查询语法介绍 递归查询主要通过`WITH`子句实现，具体格式如下： ```sql WITH recursive_query_name (column_list) AS ( -- 递归基 SELECT ... FROM table_name WHERE base_condition UNION ...
本文将深入探讨一个具体的案例，即如何利用SQL自定义函数实现对部门层级数据的递归查询，这不仅能够帮助我们更好地理解SQL递归函数的工作原理，还能提供一个实际应用的例子。 ### SQL自定义递归方法详解 #### 1. ...
内容概要：本文全面介绍了Microsoft SQL Server中的高级查询技巧及其应用，涵盖复杂查询构建、子查询与嵌套查询、索引优化、查询计划分析、窗口函数、递归CTE、数据整合操作、高级聚合与分组、排序与筛选、数据安全...
### Oracle 递归查询详解及实例 #### 一、引言 在数据库查询语言中，Oracle 提供了一种强大的功能——递归查询，这在其他数据库系统如 SQL Server 中是缺失的功能。递归查询允许用户执行多级关联查询，特别适用于...
### MySQL 递归查询知识点详解 #### 一、MySQL 递归查询概述 在数据库查询中，递归查询主要用于处理层级或树形结构的数据。这类数据的特点是每一项记录不仅包含自身的信息，还可能与其他记录形成上下级关系。MySQL...
### SQL 递归查询知识点详解 #### 一、背景与目的 在数据库处理过程中，有时我们需要对具有层次结构的数据进行查询。例如，在本案例中，我们有一个简单的表，该表包含三列：`A`、``B` 和 `Value`。其中 `A` 列与 `B...
在Oracle数据库中，SQL递归查询是一种非常有用的工具，它允许我们处理层级关系的数据，例如组织结构、树形目录或有上下级关联的数据。本文将详细介绍如何在Oracle中使用SQL递归语句，并通过示例进行说明。 首先，...
### MySQL多种递归查询方法详解 #### 一、Oracle中的递归查询方法 在Oracle数据库中，递归查询可以通过`START WITH CONNECT BY PRIOR`语句实现。此语句允许用户按照树状结构来检索数据。 ##### 1. `START WITH ...
### Oracle递归查询详解 #### 一、引言 在处理具有层级结构的数据时，递归查询是一项非常有用的技能。例如，在处理组织架构、产品分类等数据时，我们经常需要查询某一节点及其所有子节点或者从某个节点追溯到其根...
### 递归查询存储过程详解 #### 一、引言 在数据库操作中，递归查询是一种非常有用的技术，尤其当需要处理具有层次结构的数据时。递归查询可以通过单个SQL语句遍历多层数据关系，从而简化复杂的查询逻辑。本文将...
### Oracle中的递归查询详解 #### 一、引言 在数据库管理中，处理具有层次结构的数据是一项常见的任务。例如，在组织结构、产品分类或文件系统等场景中，经常需要查询这种类型的层级数据。Oracle数据库提供了强大...
逻辑查询处理步骤详解 步骤1：执行笛卡尔乘积（交叉联接） 步聚2：应用ON筛选器（联接条件） 步骤3：添加外部行（Outer Row） 步骤4：应用WHERE筛选器 步骤5：分组 步骤6：应用CUBE或ROLLUP选项 ...
### MySQL 递归查询树型结构的代码逻辑详解 在处理具有层次结构的数据时，例如组织架构、分类信息等场景，经常会遇到需要构建树形结构数据的需求。这种情况下，使用MySQL进行递归查询是一种非常有效的手段。下面...
SQL Server从2005版开始引入了一种新的查询方式——递归公共表表达式（Recursive Common Table Expression, CTE），它可以高效地处理树形查询，无需循环。 递归CTE的核心在于`WITH`关键字，它允许我们定义一个临时...
1.3 逻辑查询处理阶段详解 1.3.1 步骤1：FROM阶段 1.3.2 步骤2：WHERE阶段 1.3.3 步骤3：GROUP BY阶段 1.3.4 步骤4：HAVING阶段 1.3.5 步骤5：SELECT阶段 1.3.6 步骤6：排序用的ORDER BY阶段 1.4 逻辑查询...
### DB2循环递归查询详解 #### 一、引言 在数据库操作中，经常会遇到需要处理层次结构数据的情况，例如组织结构、产品分类等。这些数据的特点是具有明显的层级关系，而传统的SQL查询往往难以高效地处理这类问题。...
### Oracle Start With.Connect By Prior 子句实现递归查询 #### 概述 在Oracle数据库中，`Start With.Connect By Prior`子句是执行递归查询的一种强大工具，主要用于处理层次结构数据。这类数据通常存在于组织...