58.Oracle数据库SQL开发之 高级查询——层次化查询
欢迎转载，转载请标明出处：http://blog.csdn.net/notbaron/article/details/49847021
先执行如下：
store@PDB1> select * from more_employees;
EMPLOYEE_ID MANAGER_ID FIRST_NAMELAST_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：
1. 使用CONNECT BY和START WITH子句
SELECT语句的CONNECT BY和START WITH子句的语法如下所示：
SELECT [LEVEL]，column,expression,…
FROM table
[WHERE where_clause]
[[START WITH start_condition] [CONNECTBY PRIOR prior_condition]];
其中，LEVEL是一个 伪列
Start_condition定义了层次化查询的起点。
Prior_condition定义了父行和子行之间的关系。编写层次化查询时必须定义CONNECT BY PRIOR子句。
例如执行：
store@PDB1>select employee_id,manager_id,first_name,last_name from more_employees startwith employee_id = 1 connect by prior employee_id=manager_id;
EMPLOYEE_IDMANAGER_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 rowsselected.
2. 使用伪列LEVEL
使用伪列LEVEL显示节点在树中的层次：
store@PDB1> selectlevel,employee_id,manager_id,first_name,last_name from more_employees startwith employee_id=1 connect by prior employee_id=manager_id order by level;
LEVEL EMPLOYEE_ID MANAGER_ID FIRST_NAME LAST_NAME
---------- ----------- -------------------- ----------
1 1 James Smith
2 10 1Kevin Black
2 2 1Ron Johnson
2 4 1Susan Jones
3 13 10 Doreen Penn
3 7 4John Grey
3 11 10 Keith Long
3 5 2Rob Green
3 3 2Fred Hobbs
3 12 10 Frank Howard
3 6 4Jane Brown
4 8 7Jean Blue
4 9 6Henry Heyson
13 rows selected.
使用COUNT函数和LEVEL来获得树中的层次数
store@PDB1> select count(distinct level) frommore_employees start with employee_id = 1 connect by prioremployee_id=manager_id;
COUNT(DISTINCTLEVEL)
--------------------
4
3. 格式化层次化查询的结果
可以用LEVEL和LPAD函数对层次化查询结果进行格式化处理，方法是在数据的左边填充字符。
store@PDB1> set pagesize 999
store@PDB1> column employee format a25
store@PDB1> select level,
lpad('',2*level-1)||first_name||' '||last_name as employee from more_employees
start withemployee_id=1 connect by prior employee_id = manager_id;
LEVEL EMPLOYEE
---------- -------------------------
1 JamesSmith
2 RonJohnson
3 FredHobbs
3 RobGreen
2 Susan Jones
3 JaneBrown
4 Henry Heyson
3 JohnGrey
4 Jean Blue
2 Kevin Black
3 KeithLong
3 FrankHoward
3 DoreenPenn
13 rows selected.
4. 从非根节点开始遍历
执行如下：
select level,
lpad('',2*level-1)||first_name||' '||last_name as employee from more_employees
startwith last_name=
'Jones'connect by prior employee_id=manager_id;
LEVEL EMPLOYEE
---------- -------------------------
1 SusanJones
2 Jane Brown
3 HenryHeyson
2 John Grey
3 JeanBlue
5. 在START WITH子句中使用子查询
在START WITH子句中可以使用子查询。
store@PDB1> select level,
lpad('',2*level-1)||first_name||' '||last_name as employee from more_employees
start withemployee_id=(
selectemployee_id from more_employees
wherefirst_name='Kevin'
andlast_name='Black')
connectby prior employee_id=manager_id;
LEVEL EMPLOYEE
---------- -------------------------
1 KevinBlack
2 Keith Long
2 Frank Howard
2 Doreen Penn
6. 从下向上遍历树
不一定非要按照从父节点到子节点的顺序从上至下遍历树。
也可以从给某个子节点开始，自下而上遍历。实现的方法是交换父节点与子节点在CONNECT BY PRIOR子句中的顺序。
执行如下：
select level,
lpad('',2*level-1)||first_name||' '||last_name as employee from more_employees
start withlast_name=
'Blue'connect by prior manager_id=employee_id;
LEVEL EMPLOYEE
---------- -------------------------
1 JeanBlue
2 John Grey
3 Susan Jones
4 JamesSmith
7. 从层次查询中删除节点和分支
select level,
lpad('',2*level-1)||first_name||' '||last_name as employee from more_employees
wherelast_name !=
'Johnson'start with employee_id=1 connect by prior employee_id=manager_id;
LEVEL EMPLOYEE
---------- -------------------------
1 JamesSmith
3 FredHobbs
3 RobGreen
2 Susan Jones
3 JaneBrown
4 Henry Heyson
3 JohnGrey
4 Jean Blue
2 Kevin Black
3 KeithLong
3 FrankHoward
3 DoreenPenn
12 rows selected.
为了将整个分支都从查询结果中除去，可以再CONNECT BY PRIOR子句中使用AND子句。
select level,lpad(' ',2*level-1)||first_name||''||last_name as employee from more_employees
start withemployee_id=1 connect by prior employee_id = manager_id
andlast_name != 'Johnson';
LEVEL EMPLOYEE
---------- -------------------------
1 JamesSmith
2 Susan Jones
3 JaneBrown
4 Henry Heyson
3 JohnGrey
4 Jean Blue
2 Kevin Black
3 KeithLong
3 FrankHoward
3 DoreenPenn
10 rows selected.
8. 在层次化查询中加入其它条件
使用WHERE子句可以再层次化查询中加入其它条件。
执行如下：
store@PDB1> select level,lpad('',2*level-1)||first_name||' '||last_name as employee,salary from more_employeeswhere salary <= 50000 start with employee_id = 1 connect by prior employee_id = manager_id;
LEVEL EMPLOYEE SALARY
---------- -----------------------------------
3 RobGreen 40000
3 JaneBrown 45000
4 Henry Heyson 30000
3 JohnGrey 30000
4 Jean Blue 29000
3 KeithLong 50000
3 FrankHoward 45000
3 DoreenPenn 47000
8 rows selected.
再分享一下我老师大神的人工智能教程吧。零基础！通俗易懂！风趣幽默！还带黄段子！希望你也加入到我们人工智能的队伍中来！http://www.captainbed.net