[Oracle数据库学习]十九、分级查询

D13

分级查询

employee表中的数据，employee_id和manager_id形成了树形结构。

 

SELECT [LEVEL], column, expr...
FROM table
[WHERE condition(s)]
[START WITH condition(s)]
[CONNECT BY PRIOR condition(s)] ;

WHERE条件

expr comparison_operator expr

 

遍历树

始点

指定需要满足的条件

START WITH column1 = value

示例：

.. START WITH last_name = 'Kochhar'

注：从指定的姓名的员工开始遍历。

 

遍历树

CONNECT BY PRIOR column1 = column2

示例：

.. CONNECT BY PRIOR employee_id = manager_id

注：这里employee_id是Parent Key，所以是从顶到底。

 

方向

等号前后的列指定了方向。

从顶到底

column1为Parent Key，column2为Child Key；

示例：

SELECT employee_id, last_name, job_id, manager_id
FROM hr.employees
START WITH employee_id = 101
CONNECT BY PRIOR  employee_id  = manager_id ;

EMPLOYEE_ID	LAST_NAME	JOB_ID	MANAGER_ID
101	Kochhar	AD_VP	100
108	Greenberg	FI_MGR	101
109	Faviet	FI_ACCOUNT	108
110	Chen	FI_ACCOUNT	108
111	Sciarra	FI_ACCOUNT	108
112	Urman	FI_ACCOUNT	108
113	Popp	FI_ACCOUNT	108
200	Whalen	AD_ASST	101
203	Mavris	HR_REP	101
204	Baer	PR_REP	101
205	Higgins	AC_MGR	101
206	Gietz	AC_ACCOUNT	205

12 rows selected.

注：从顶到底即从manager到employee。

 

从底到顶

column1为Child Key，column2为Parent Key

示例：

SELECT employee_id, last_name, job_id, manager_id
FROM hr.employees
START WITH employee_id = 101
CONNECT BY PRIOR manager_id = employee_id ;

EMPLOYEE_ID	LAST_NAME	JOB_ID	MANAGER_ID
101	Kochhar	AD_VP	100
100	King	AD_PRES	-

2 rows selected.

注：从底到顶即从empoloyee到manager

 

使用LEVEL 伪列标记层次

SELECT LPAD(last_name, LENGTH(last_name)+(LEVEL*2)-2,'_') AS org_chart
FROM hr.employees 
START WITH last_name='King' 
CONNECT BY PRIOR employee_id = manager_id

ORG_CHART

King

King

__Kochhar

____Greenberg

______Faviet

______Chen

______Sciarra

______Urman

______Popp

____Whalen

____Mavris

____Baer

____Higgins

______Gietz

__De Haan

____Hunold

______Ernst

______Austin

______Pataballa

______Lorentz

__Raphaely

____Khoo

____Baida

____Tobias

____Himuro

____Colmenares

__Weiss

____Nayer

____Mikkilineni

____Landry

____Markle

____Taylor

____Fleaur

____Sullivan

____Geoni

__Fripp

____Bissot

____Atkinson

____Marlow

____Olson

____Sarchand

____Bull

____Dellinger

____Cabrio

__Kaufling

____Mallin

____Rogers

____Gee

____Philtanker

____Chung

Rows 1 - 50. More rows exist.

注：lpad函数是Oracle数据库函数，lpad函数从左边对字符串使用指定的字符进行填充。

语法格式如下：

lpad( string, padded_length, [ pad_string ] )

string：准备被填充的字符串；

padded_length：填充之后的字符串长度，也就是该函数返回的字符串长度，如果这个数量比原字符串的长度要短，lpad函数将会把字符串截取成从左到右的n个字符;

pad_string：填充字符串，是个可选参数，这个字符串是要粘贴到string的左边，如果这个参数未写，lpad函数将会在string的左边粘贴空格。

转自：https://blog.csdn.net/yulei_qq/article/details/17389901

 

“修剪树枝”

示例：

SELECT LPAD(last_name, LENGTH(last_name)+(LEVEL*2)-2,'_') AS org_chart
FROM hr.employees 
START WITH last_name='Kochhar' 
CONNECT BY PRIOR employee_id = manager_id;

ORG_CHART

Kochhar

__Greenberg

____Faviet

____Chen

____Sciarra

____Urman

____Popp

__Whalen

__Mavris

__Baer

__Higgins

____Gietz

12 rows selected.

 

使用WHERE子句删除节点（leaf）

SELECT LPAD(last_name, LENGTH(last_name)+(LEVEL*2)-2,'_') AS org_chart
FROM hr.employees 
WHERE last_name != 'Greenberg'
START WITH last_name='Kochhar' 
CONNECT BY PRIOR employee_id = manager_id;

ORG_CHART

Kochhar

____Faviet

____Chen

____Sciarra

____Urman

____Popp

__Whalen

__Mavris

__Baer

__Higgins

____Gietz

11 rows selected.

 注：与上面相比，只少了”__Greenberg“一行，即删除掉一个叶子。

 

使用CONNECT BY子句删除树枝（branch）

SELECT LPAD(last_name, LENGTH(last_name)+(LEVEL*2)-2,'_') AS org_chart
FROM hr.employees 
START WITH last_name='Kochhar' 
CONNECT BY PRIOR employee_id = manager_id 
AND last_name != 'Greenberg';

ORG_CHART

Kochhar

__Whalen

__Mavris

__Baer

__Higgins

____Gietz

6 rows selected.

注：与第一次查询相比，少了6行（”Greenberg“及其子树），说明删除了”树枝“。

 

 

总结：

本节介绍了对具有层次关系的数据创建树形报表：

1）指定始点（START WITH）和方向（CONNECT BY，Parent Key和Child Key的位置）；

2）删除节点（WHERE）和树枝（CONNECT BY）；

3）使用LPAD修改树形显示。