oracle树查询

我们用一张简单的表COMP_INFO来存储单位信息，包含上下级隶属关系，如下三列，分别为单位ID，单位名称，上级单位ID：

CREATE TABLE COMP_INFO

(

  COMP_ID      VARCHAR2(5 BYTE)                 NOT NULL,

  COMP_NAME    VARCHAR2(80 BYTE)                NOT NULL,

  HIGHER_COMP  VARCHAR2(5 BYTE)

)

TABLESPACE USERS

PCTUSED    0

PCTFREE    10

INITRANS   1

MAXTRANS   255

STORAGE    (

            INITIAL          64K

            MINEXTENTS       1

            MAXEXTENTS       2147483645

            PCTINCREASE      0

            BUFFER_POOL      DEFAULT

           )

LOGGING

NOCACHE

NOPARALLEL

NOMONITORING;

插入初始数据：

INSERT INTO COMP_INFO VALUES('10000','XX公司','');

INSERT INTO COMP_INFO VALUES('11000','XX公司英国公司','10000');

INSERT INTO COMP_INFO VALUES('12000','XX公司中国公司','10000');

INSERT INTO COMP_INFO VALUES('13000','XX公司日本公司','10000');

INSERT INTO COMP_INFO VALUES('12100','XX公司中国公司北京研究中心','12000');

INSERT INTO COMP_INFO VALUES('12200','XX公司中国公司上海办事处','12000');

INSERT INTO COMP_INFO VALUES('12110','XX公司中国公司北京研究一所','12100');

INSERT INTO COMP_INFO VALUES('12120','XX公司中国公司北京研究二所','12100');

INSERT INTO COMP_INFO VALUES('12130','XX公司中国公司北京研究三所','12100');

INSERT INTO COMP_INFO VALUES('12140','XX公司中国公司北京研究四所','12100');

我们先查询一下select * from COMP_INFO：

COMP_ID	COMP_NAME	HIGHER_COMP
10000	XX公司
11000	XX公司英国公司	10000
12000	XX公司中国公司	10000
13000	XX公司日本公司	10000
12100	XX公司中国公司北京研究中心	12000
12200	XX公司中国公司上海办事处	12000
12110	XX公司中国公司北京研究一所	12100
12120	XX公司中国公司北京研究二所	12100
12130	XX公司中国公司北京研究三所	12100
12140	XX公司中国公司北京研究四所	12100

 

 

树查询语句的语法如下

SELECT [LEVEL], column, expr...

FROM table

[WHERE condition(s)]

[START WITH condition(s)]

[CONNECT BY PRIOR condition(s)]

接下来进行一些实际查询练习：（顶——底）

1、   查询整整个树的层次结构

select LPAD(' ',8*(LEVEL-1))||(select b.comp_name as from comp_info b where b.comp_id=a.higher_comp) 上级单位,

a.comp_name 单位名称 from comp_info a

start with higher_comp is null

connect by PRIOR comp_id=  higher_comp

结果：

上级公司	公司名称
	XX公司
XX公司	XX公司英国公司
XX公司	XX公司中国公司
XX公司中国公司	XX公司中国公司北京研究中心
XX公司中国公司北京研究中心	XX公司中国公司北京研究一所
XX公司中国公司北京研究中心	XX公司中国公司北京研究二所
XX公司中国公司北京研究中心	XX公司中国公司北京研究三所
XX公司中国公司北京研究中心	XX公司中国公司北京研究四所
XX公司中国公司	XX公司中国公司上海办事处
XX公司	XX公司日本公司

 

2、查询北京研究中心及下属单位：（顶——底）

select LPAD(' ',8*(LEVEL-1))||(select b.comp_name as from comp_info b where b.comp_id=a.higher_comp) 上级单位,

a.comp_name 单位名称 from comp_info a

start with comp_id='12100'

connect by PRIOR comp_id=  higher_comp

结果：

上级公司	公司名称
XX公司中国公司	XX公司中国公司北京研究中心
XX公司中国公司北京研究中心	XX公司中国公司北京研究一所
XX公司中国公司北京研究中心	XX公司中国公司北京研究二所
XX公司中国公司北京研究中心	XX公司中国公司北京研究三所
XX公司中国公司北京研究中心	XX公司中国公司北京研究四所

 

3、   查询北京研究中心的上溯单位：（底——顶）

select LPAD(' ',8*(LEVEL-1))||(select b.comp_name as from comp_info b where b.comp_id=a.higher_comp) 上级单位,

a.comp_name 单位名称 from comp_info a

start with comp_id='12100'

connect by higher_comp= PRIOR  comp_id

结果：

上级公司	公司名称
XX公司中国公司	XX公司中国公司北京研究中心
XX公司	XX公司中国公司
	XX公司

 

4、   查询北京研究中心的下属单位：（顶——底）

select LPAD(' ',8*(LEVEL-1))||(select b.comp_name as from comp_info b where b.comp_id=a.higher_comp) 上级单位,

a.comp_name 单位名称 from comp_info a

start with higher_comp='12100'

connect by PRIOR  comp_id= higher_comp

 

结果：

上级公司	公司名称
XX公司中国公司北京研究中心	XX公司中国公司北京研究一所
XX公司中国公司北京研究中心	XX公司中国公司北京研究二所
XX公司中国公司北京研究中心	XX公司中国公司北京研究三所
XX公司中国公司北京研究中心	XX公司中国公司北京研究四所

 

5、   查询二级单位为北京研究中心的单位的上溯单位：（底——顶）

select LPAD(' ',8*(LEVEL-1))||(select b.comp_name as from comp_info b where b.comp_id=a.higher_comp) 上级单位,

a.comp_name 单位名称 from comp_info a

start with higher_comp='12100'

connect by comp_id=PRIOR higher_comp

结果：

上级公司	公司名称
XX公司中国公司北京研究中心	XX公司中国公司北京研究一所
XX公司中国公司	XX公司中国公司北京研究中心
XX公司	XX公司中国公司
	XX公司
XX公司中国公司北京研究中心	XX公司中国公司北京研究二所
XX公司中国公司	XX公司中国公司北京研究中心
XX公司	XX公司中国公司
	XX公司
XX公司中国公司北京研究中心	XX公司中国公司北京研究三所
XX公司中国公司	XX公司中国公司北京研究中心
XX公司	XX公司中国公司
	XX公司
XX公司中国公司北京研究中心	XX公司中国公司北京研究四所
XX公司中国公司	XX公司中国公司北京研究中心
XX公司	XX公司中国公司
	XX公司