Oracle 层次化查询

最新推荐文章于 2024-09-30 17:19:22 发布

cikjohq634317

最新推荐文章于 2024-09-30 17:19:22 发布

阅读量95

点赞数

文章标签：数据库

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

view plaincopy to clipboardprint?
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.
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。
如下所示

view plaincopy to clipboardprint?
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.
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

view plaincopy to clipboardprint?
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.
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来获取数中的层次数

view plaincopy to clipboardprint?
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
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填充不同个数的空格，从而缩进显示员工的名字。

view plaincopy to clipboardprint?
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.
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，以此类推。

view plaincopy to clipboardprint?
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
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子句。

view plaincopy to clipboardprint?
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
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，以此类推。

view plaincopy to clipboardprint?
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>
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

view plaincopy to clipboardprint?
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.
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及其所有下属从结果中除去

view plaincopy to clipboardprint?
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.
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的员工

view plaincopy to clipboardprint?
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>
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>

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/wh62592855/archive/2009/10/21/4704104.aspx

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/256370/viewspace-1028397/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/256370/viewspace-1028397/