目 录
一、测试准备
二、实现各种查询要求
三、要点总结
正 文
一、测试准备
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,
PARENT_ID NUMBER(2) ,
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来为每个层级的字段提供不同的缩进。
注意，写的时候要给遍历的那列起个别名。
分享到：
相关推荐
树和图在关系型数据库中的表示及遍历
在树的遍历中，我们按照一定的顺序访问树中的每一个节点，确保每个节点仅被访问一次。通常，树的遍历有三种主要方式： 1. 前序遍历（Preorder Traversal）：首先访问根节点，然后递归地遍历左子树，最后遍历右子树...
在遍历数据库之前，我们需要建立数据库连接并执行查询。这通常涉及以下步骤： 1. 引用相关数据库访问库，如ADO.NET（System.Data.SqlClient或System.Data.OleDb）。 2. 创建连接字符串，指明数据库类型、服务器地址...
在树的遍历中，我们可以定义一个函数，该函数接受一个节点作为参数，然后依次处理当前节点及其所有子节点。根据遍历顺序的不同，主要有前序遍历、中序遍历和后序遍历三种方式： 1. 前序遍历：先访问根节点，再遍历...
在SQL中，由于数据库中的数据通常是表的形式，我们无法直接创建和遍历树结构。然而，通过自连接和层次查询，可以模拟树的遍历。例如，我们可以为每个节点定义一个`parent_id`字段来表示其父节点，然后通过递归查询...
在软件开发中，树的遍历是许多关键算法的基础，例如在编译器中解析语法树、在数据库中构建和查询索引、在图形用户界面中组织组件等。了解并熟练掌握树的遍历对于提升编程能力、解决复杂问题至关重要。 在这个“树的...
在IT领域，树是一种非常重要的数据结构，广泛应用于软件开发、数据库索引、图形学、编译器设计等多个方面...同时，对于压缩包内的源代码，你可以通过运行和调试来加深对树的遍历概念的理解，并将其应用到自己的项目中。
本话题主要聚焦于树的遍历及其节点信息的显示，尤其针对Windows编程中的CTreeCtrl控件进行探讨。 树结构由节点构成，每个节点可以有零个或多个子节点，这种层次关系使得树成为存储和处理分层数据的理想选择。节点...
在Java中遍历MySQL数据库中的树形结构是一项常见的任务，尤其是在处理组织结构、文件系统或任何具有层次关系的数据时。本文将深入探讨如何利用Java语言和MySQL数据库来实现这一功能，解析给定代码片段，并提供一种...
在给定的压缩包文件中，文件名列表包括"**WUQIN1.C**"、"**WUQIN.C**"、"**LUQI.C**"以及"**www.pudn.com.txt**"，这些可能是实现树遍历或图搜索算法的C语言源代码。WUQIN和LUQI可能代表不同的遍历方法，而...
在这个场景中，我们关注的是如何在C#编程环境下处理父子关系树（通常称为层级数据或树形结构），并通过递归方法进行遍历。这样的操作常见于构建组织结构、文件系统或者产品结构（如物料清单BOM）。下面我们将深入...
总结，树状数据（多叉树）在数据库中的存储涉及数据表设计、数据转化、查询操作以及维护一致性等多个方面。通过理解这些知识点，并结合提供的源码，我们可以实现高效地在MySQL中存储和操作多叉树结构。这在实际项目...
学习数据库可用手段，用了多种方法实现了树的非递归遍历。
在层次遍历中，我们按照层级顺序访问节点，即从根节点开始，然后依次访问其子节点，接着是子节点的子节点，以此类推。 C语言实现层次遍历通常使用队列（Queue）作为辅助数据结构。队列是一种先进先出（FIFO）的数据...
在IT领域，树是一种非常重要的数据结构，广泛应用于算法设计、...通过阅读和分析这些代码，可以加深对树遍历和线索化的理解，解决实际操作中的疑问。对于初学者而言，理解并掌握这些知识将对提升编程能力大有裨益。
在后序遍历中，我们首先访问左子树和右子树，然后访问根结点。例如，在二叉树中，如果根结点的值为A，左子树的值为B，右子树的值为C，那么后序遍历的结果将是B C A。 在本文的示例代码中，我们使用了C语言来实现...
通过对这些方法的了解和应用，开发者可以更加高效地管理和查询存储在数据库中的树形数据结构，从而在实际工作中提升数据处理的能力和效率。同时，这些方法也能够帮助开发者深入理解树形数据结构的特点和遍历算法的...
### PHP 数据库树的遍历方法 在本篇内容中，我们将探讨如何使用 PHP 对数据库中的树状结构进行遍历。树形结构是一种常见的数据组织形式，在很多应用场景中都有广泛的应用，比如文件系统、组织架构等。对于树形结构...
SNMPv3提供了身份验证、加密和访问控制等安全特性，更适合在对安全性要求高的环境中使用。 在实现SNMP管理时，需要考虑以下几点： 1. 安全性：确保SNMP通信的安全性，使用适当的认证和加密机制。 2. 性能：避免频繁...