在数据库中对树进行遍历查询


目 录
一、测试准备
二、实现各种查询要求
三、要点总结


正 文
一、测试准备
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