oracle中的层次化查询

来源：互联网发布：夏河淘宝店编辑：程序博客网时间：2024/05/23 19:10

记得以前在论坛里看到wildwave在回一个朋友的贴时候使用了start with,connect by。当时我一头雾水，从来没见过这两个关键字，网上搜了搜，讲的也不太详细，呵呵。今天看书的时候在目录中看到了，直接跳过前面内容翻到这个地方一睹为快。下面我就边做实验，边说说自己的学习成果吧。

实验中使用的表more_employees中的内容如下

[c-sharp] view plaincopy

SQL> set pagesize 10000
SQL> select * from more_employees;
EMPLOYEE_ID MANAGER_ID FIRST_NAME LAST_NAME TITLE SALARY
----------- ---------- ---------- ---------- -------------------- ----------
1 James Smith CEO 800000
2 1 Ron Johnson Sales Manager 600000
3 2 Fred Hobbs Sales Person 200000
4 1 Susan Jones Support Manager 500000
5 2 Rob Green Sales Person 40000
6 4 Jane Brown Support Person 45000
7 4 John Grey Support Manager 30000
8 7 Jean Blue Support Person 29000
9 6 Henry Heyson Support Person 30000
10 1 Kevin Black Ops Manager 100000
11 10 Keith Long Ops Person 50000
12 10 Frank Howard Ops Person 45000
13 10 Doreen Penn Ops Person 47000
13 rows selected.

1. 使用CONNECT BY和START WITH子句

SELECT语句中的CONNECT BY和START WITH子句的语法如下

SELECT [LEVEL],column,expression, ...

FROM table

[WHERE where_clause]

[[START WITH start_condition] [CONNECT BY PRIOR prior_condition]];

其中：

LEVEL是一个“伪列”，代表树的第几层。对根节点来说，LEVEL返回1，根节点的子节点返回2，以此类推。
start_condition定义了层次化查询的起点。当编写层次化查询的时候必须指定START WITH子句。例如，可以将start_condition定义为employee=1，表示从员工#1开始。
prior_condition定义了父行和子行的关系。当编写层次化查询时必须定义CONNECT BY PRIOR子句。例如，可以将prior_condition定义为employee_id=manager_id，表示父节点的employee_id和子节点的manager_id之间存在关系，也就是说，子节点的manager_id指向父节点的employee_id。

如下所示

[c-sharp] view plaincopy

SQL> select employee_id,manager_id,first_name,last_name
2 from more_employees
3 START WITH employee_id=1
4 CONNECT BY PRIOR employee_id=manager_id;
EMPLOYEE_ID MANAGER_ID FIRST_NAME LAST_NAME
----------- ---------- ---------- ----------
1 James Smith
2 1 Ron Johnson
3 2 Fred Hobbs
5 2 Rob Green
4 1 Susan Jones
6 4 Jane Brown
9 6 Henry Heyson
7 4 John Grey
8 7 Jean Blue
10 1 Kevin Black
11 10 Keith Long
12 10 Frank Howard
13 10 Doreen Penn
13 rows selected.

2.使用伪列LEVEL

[c-sharp] view plaincopy

SQL> select LEVEL,employee_id,manager_id,first_name,last_name
2 from more_employees
3 START WITH employee_id=1
4 CONNECT BY PRIOR employee_id=manager_id;
LEVEL EMPLOYEE_ID MANAGER_ID FIRST_NAME LAST_NAME
---------- ----------- ---------- ---------- ----------
1 1 James Smith
2 2 1 Ron Johnson
3 3 2 Fred Hobbs
3 5 2 Rob Green
2 4 1 Susan Jones
3 6 4 Jane Brown
4 9 6 Henry Heyson
3 7 4 John Grey
4 8 7 Jean Blue
2 10 1 Kevin Black
3 11 10 Keith Long
3 12 10 Frank Howard
3 13 10 Doreen Penn
13 rows selected.

下面这个查询使用COUNT()和LEVEL来获取数中的层次数

[c-sharp] view plaincopy

SQL> select count(distinct LEVEL)
2 from more_employees
3 START WITH employee_id=1
4 CONNECT BY PRIOR employee_id=manager_id;
COUNT(DISTINCTLEVEL)
--------------------
4

3.格式化层次化查询的结果

可以用 LEVEL和LPAD函数对层次化查询结果进行格式化处理，方法是在数据的左边填补字符。如下例所示，根据不同LEVEL填充不同个数的空格，从而缩进显示员工的名字。

[c-sharp] view plaincopy

SQL> select LEVEL,LPAD(' ',2*LEVEL-1) || first_name || ' ' ||last_name as employ
ee
2 from more_employees
3 START WITH employee_id=1
4 CONNECT BY PRIOR employee_id=manager_id;
LEVEL EMPLOYEE
---------- -------------------------
1 James Smith
2 Ron Johnson
3 Fred Hobbs
3 Rob Green
2 Susan Jones
3 Jane Brown
4 Henry Heyson
3 John Grey
4 Jean Blue
2 Kevin Black
3 Keith Long
3 Frank Howard
3 Doreen Penn
13 rows selected.

4.从非根节点开始遍历

对树进行遍历不一定要从根节点开始；使用START WITH子句可以从任何节点开始。下面这个查询就是从Susan Jones开始；注意，Susan Jones的LEVEL返回1，Jane Brown的LEVEL返回2，以此类推。

[c-sharp] view plaincopy

SQL> select LEVEL,LPAD(' ',2*LEVEL-1) || first_name || ' ' ||last_name as employ
ee
2 from more_employees
3 START WITH last_name='Jones'
4 CONNECT BY PRIOR employee_id=manager_id;
LEVEL EMPLOYEE
---------- -------------------------
1 Susan Jones
2 Jane Brown
3 Henry Heyson
2 John Grey
3 Jean Blue

5.在START WITH中使用子查询

下面这个查询使用子查询来选择名为Kevin Black的员工的employee_id。然后传给START WITH子句。

[c-sharp] view plaincopy

SQL> select LEVEL,LPAD(' ',2*LEVEL-1) || first_name || ' ' ||last_name as employ
ee
2 from more_employees
3 START WITH employee_id=(
4 select employee_id
5 from more_employees
6 where first_name='Kevin'
7 and last_name='Black'
8 )
9 CONNECT BY PRIOR employee_id=manager_id;
LEVEL EMPLOYEE
---------- -------------------------
1 Kevin Black
2 Keith Long
2 Frank Howard
2 Doreen Penn

6.从下向上遍历树

不一定非要按照从父节点到子节点的顺序从上至下遍历树；也可以从某个子节点开始，从下而上遍历。实现的方法是交换父节点和子节点在CONNECT BY PRIOR子句中的顺序。例如，CONNECT BY PRIOR manager_id=employee_id可以将父节点的manager_id连接到子节点的employee_id上。

下面这个查询从Jean Blue开始，向上遍历，直到James Smith为止；注意，Jean Blue的LEVEL返回1，John Grey的LEVEL返回2，以此类推。

[c-sharp] view plaincopy

SQL> select LEVEL,LPAD(' ',2*LEVEL-1) || first_name || ' ' ||last_name as employ
ee
2 from more_employees
3 START WITH last_name='Blue'
4 CONNECT BY PRIOR manager_id=employee_id;
LEVEL EMPLOYEE
---------- -------------------------
1 Jean Blue
2 John Grey
3 Susan Jones
4 James Smith
SQL>

7.从层次化查询中删除节点和分支

可以用WHERE子句从查询树中除去某个特定的节点，下面这个查询使用WHERE last_name!='Johnson'子句从结果中除去Ron Johnson

[c-sharp] view plaincopy

SQL> select LEVEL,LPAD(' ',2*LEVEL-1) || first_name || ' ' ||last_name as employ
ee
2 from more_employees
3 where last_name!='Johnson'
4 START WITH employee_id=1
5 CONNECT BY PRIOR employee_id=manager_id;
LEVEL EMPLOYEE
---------- -------------------------
1 James Smith
3 Fred Hobbs
3 Rob Green
2 Susan Jones
3 Jane Brown
4 Henry Heyson
3 John Grey
4 Jean Blue
2 Kevin Black
3 Keith Long
3 Frank Howard
3 Doreen Penn
12 rows selected.

可以看到，尽管Ron Johnson已经从结果中除去了，但是他的下属Fred Hobbs和Rob Green仍然在结果中。为了将整个分支都从查询结果中除去，可以再CONNECT BY PRIOR子句中使用AND 子句。例如下面这个例子使用AND last_name!='Johnson'将Ron Johnson及其所有下属从结果中除去

[c-sharp] view plaincopy

SQL> select LEVEL,LPAD(' ',2*LEVEL-1) || first_name || ' ' ||last_name as employ
ee
2 from more_employees
3 START WITH employee_id=1
4 CONNECT BY PRIOR employee_id=manager_id
5 AND last_name!='Johnson';
LEVEL EMPLOYEE
---------- -------------------------
1 James Smith
2 Susan Jones
3 Jane Brown
4 Henry Heyson
3 John Grey
4 Jean Blue
2 Kevin Black
3 Keith Long
3 Frank Howard
3 Doreen Penn
10 rows selected.

8.在层次化查询中加入其它条件

使用WHERE子句可以在层次化查询中加入其它条件。下面这个例子使用WHERE子句来控制只显示工资少于等于$50000的员工

[c-sharp] view plaincopy

SQL> select LEVEL,LPAD(' ',2*LEVEL-1) || first_name || ' ' ||last_name as employ
ee,salary
2 from more_employees
3 where salary<=50000
4 START WITH employee_id=1
5 CONNECT BY PRIOR employee_id=manager_id;
LEVEL EMPLOYEE SALARY
---------- ------------------------- ----------
3 Rob Green 40000
3 Jane Brown 45000
4 Henry Heyson 30000
3 John Grey 30000
4 Jean Blue 29000
3 Keith Long 50000
3 Frank Howard 45000
3 Doreen Penn 47000
8 rows selected.
SQL>

呵呵，累死了，好了差不多就这些了，以后碰到新的内容再做补充吧~ 吃饭洗澡~

补充：

9、CONNECT BY 后面如果不加PRIOR的话，查询将不进行深层递归。

select * from t2 start with root_id = 0 connect by prior id = root_id;

|----------a-----------a1

| |

| |--------a2

|----------b-----------b1

| |

| |--------b2

select * from t2 start with root_id = 0 connect by id = root_id;

|-----------------a

|-----------------b

如果不加PRIOR关键字的话，就像上面所说到那样，不会进行深层次查询。

[c-sharp] view plaincopy

SQL> select empno,ename,mgr,level from emp
2 start with empno=7698
3 connect by mgr=empno;
EMPNO ENAME MGR LEVEL
---------- ---------- ---------- ----------
7698 BLAKE 7839 1
SQL> select empno,ename,mgr,level from emp
2 start with empno=7698
3 connect by empno=mgr;
EMPNO ENAME MGR LEVEL
---------- ---------- ---------- ----------
7698 BLAKE 7839 1

可以看到，都只显示了start with中指定的那一条记录而已。

10、运算符PRIOR被放置于等号前后的位置决定着查询时的检索顺序

下面我们看几个例子

[c-sharp] view plaincopy

//先来看一下emp表中所有记录之间的层次关系
SQL> select empno,ename,mgr,level from emp
2 start with empno=7839
3 connect by prior empno=mgr;
EMPNO ENAME MGR LEVEL
---------- ---------- ---------- ----------
7839 KING 1
7566 JONES 7839 2
7788 SCOTT 7566 3
7876 ADAMS 7788 4
7902 FORD 7566 3
7369 SMITH 7902 4
7698 BLAKE 7839 2
7499 ALLEN 7698 3
7521 WARD 7698 3
7654 MARTIN 7698 3
7844 TURNER 7698 3
EMPNO ENAME MGR LEVEL
---------- ---------- ---------- ----------
7900 JAMES 7698 3
7782 CLARK 7839 2
7934 MILLER 7782 3
14 rows selected.
//我们选取empno=7566这条记录作为起始点
SQL> select empno,ename,mgr,level from emp
2 start with empno=7566
3 connect by prior empno=mgr;
EMPNO ENAME MGR LEVEL
---------- ---------- ---------- ----------
7566 JONES 7839 1
7788 SCOTT 7566 2
7876 ADAMS 7788 3
7902 FORD 7566 2
7369 SMITH 7902 3
SQL> select empno,ename,mgr,level from emp
2 start with empno=7566
3 connect by prior mgr=empno;
EMPNO ENAME MGR LEVEL
---------- ---------- ---------- ----------
7566 JONES 7839 1
7839 KING 2
SQL> select empno,ename,mgr,level from emp
2 start with empno=7566
3 connect by mgr=prior empno;
EMPNO ENAME MGR LEVEL
---------- ---------- ---------- ----------
7566 JONES 7839 1
7788 SCOTT 7566 2
7876 ADAMS 7788 3
7902 FORD 7566 2
7369 SMITH 7902 3
SQL> select empno,ename,mgr,level from emp
2 start with empno=7566
3 connect by empno=prior mgr;
EMPNO ENAME MGR LEVEL
---------- ---------- ---------- ----------
7566 JONES 7839 1
7839 KING 2

=================================================================

使用SIBLINGS 关键字排序
    前面说了，对于层次查询如果用order by 排序，比如order by last_name 则是先做完层次获得level, 然后按last_name 排序，这样破坏了层次，比如特别关注某行的深度，按level 排序，也是会破坏层次的。
    在oracle10g 中，增加了siblings 关键字的排序。
    语法：order siblings by <expre>
    它会保护层次，并且在每个等级中按expre 排序。
    select level,
    id,last_name,manager_id
    from s_emp
    start with manager_id is null
    connect by prior id=manager_id
    order siblings by last_name;
    结果如图：

CONNECT_BY_ISCYCLE 和NOCYCLE 关键字
    如果从root 节点开始找其子孙，找到一行，结果发生和祖先互为子孙的情况，则发生循环，oracle 会报ORA-01436: CONNECT BY loop in user data，在9i 中只能将发生死循环的不加入到树中或删除，在10g 中可以用nocycle 关键字加在connect by 之后，避免循环的参加查询操作。并且通过connect_by_iscycle 得到哪个节点发生循环。0 表示未发生循环，1 表示发生了循环，如：
    create table family1(
    fatherid number,
    childid number
    );
    insert into family1 values(null,1);
    insert into family1 values(1,2);-- 父节点为1
    insert into family1 values(1,3);
    insert into family1 values(2,4);-- 发生循环
    insert into family1 values(4,1);-- 子节点为1
    insert into family1 values(4,5);
    commit;
    select connect_by_iscycle, fatherid,childid,sys_connect_by_path(childid,'/')
    from family1
    start with fatherid is null
    connect by nocycle prior childid=fatherid;
    结果是：

0 0