本篇文章详细介绍了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
分享到：
相关推荐
oracle数据库中如果需要使用SQL递归语句，应该怎么写呢？下面就为您介绍一个oracle中使用SQL递归语句的例子，供您参考。例子： 代码如下:pid id a b a c a e b b1 b b2 c c1 e e1 e e3 d d1 ...
逻辑查询处理步骤详解 步骤1：执行笛卡尔乘积（交叉联接） 步聚2：应用ON筛选器（联接条件） 步骤3：添加外部行（Outer Row） 步骤4：应用WHERE筛选器 步骤5：分组 步骤6：应用CUBE或ROLLUP选项 ...
主要介绍了SQL Server 树形表非循环递归查询的实例详解的相关资料,本文介绍的非常详细具有参考借鉴价值，需要的朋友可以参考下
《精通SQ:结构化查询语言详解》全面讲解SQL语言，提供317个典型应用，读者可以随查随用，针对SQL Server和Oracle进行讲解，很有代表性。 全书共包括大小实例317个，突出了速学速查的特色。《精通SQ:结构化查询语言...
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 逻辑查询...
递归CTE是SQL SERVER 2005中重要的增强之一。一般我们在处理树，图和层次结构的问题时需要用到递归查询...本文给大家详解使用sqlserver cte递归查询出来树、图和层次结构，本文介绍的非常详细，感兴趣的朋友一起看看吧
2.1.3 使用查询分析器执行sql语句 22 2.2 transact-sql 24 2.2.1 transact-sql 概述 24 2.2.2 transact-sql的主要组成 25 2.2.3 transact-sql的一些重要命令 26 2.3 oracle数据库 29 2.3.1 oracle数据库软件...
1.SQL递归 在SQL Server中，我们可以利用表表达式来实现递归算法，一般用于阻止机构的加载及相关性处理。 –>实现： 假设OrganiseUnit（组织机构表）中主要的三个字段为OrganiseUnitID（组织机构主键ID）、...
《Microsoft SQL Server 2008技术内幕:T-SQL查询》全面深入地介绍了Microsoft SQL Server 2008中高级T-SQL查询、性能优化等方面的内容，以及SQL Server 2008新增加的一些特性。主要内容包括SQL的基础理论、查询优化...
《Microsoft SQL Server 2008技术内幕:T-SQL查询》全面深入地介绍了Microsoft SQL Server 2008中高级T-SQL查询、性能优化等方面的内容，以及SQL Server 2008新增加的一些特性。主要内容包括SQL的基础理论、查询优化...
12.2.2 避免子查询的错误执行计划 350 12.2.3 所在环境的资源不足等问题 351 12.3 本章习题、总结与延伸 351 第13章 动手，过程函数优化让SQL飞 352 13.1 PL/SQL优化重点 353 13.1.1 定义类型的优化 353 ...
12.2.2 避免子查询的错误执行计划 350 12.2.3 所在环境的资源不足等问题 351 12.3 本章习题、总结与延伸 351 第13章 动手，过程函数优化让SQL飞 352 13.1 PL/SQL优化重点 353 13.1.1 定义类型的优化 353 ...
1. 能够在select等SQL语句中直接使用自定义函数，存储过程不行。 2. 自定义函数可以调用其他函数，也可以调用自己（递归） 3. 可以在表列和 CHECK 约束中使用自定义函数来实现特殊列或约束 4. 自定义函数不能有...
3. 资源的概念及发送sql查询 109 4. php修改表数据 112 5. php查询表数据 113 6. 查询与修改综合运用 118 7. mysql调试技巧 120 六、 贴吧项目 123 1. 贴吧项目之建表 123 2. include_require详解 124 3. 魔术引号与...
UML类图详解.mht 中兴SQL题目.doc 中兴面试--公共部分.doc 人事面试100问.doc 你一定要给我记住啊.docx 好多不懂的Servlet以及Jsp.doc 学习笔记.doc 实践50.doc 深圳信狮职业培训学校java面试题.doc 第2章 递归与...
PHP实现动态执行代码的方法PHP执行linux命令常用函数汇总crontab无法执行php的解决方法4种PHP异步执行的常用方式详解PHP执行定时任务的实现思路PHP定时执行任务的3种方法详解PHP执行SQL文件并将SQL文件导入到数据库...
虽然有很多种方式可以解决这个问题，但是我们可以用T-SQL代码来处理这个文件删除过程。我用xp_cmdshell命令和FORFILES命令来处理类似于你所列出的过程。可以从Microsoft TechNet中获得FORFILES的可靠信息，但是在这...