oracle树查询

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

CREATETABLE COMP_INFO

(

  COMP_ID     VARCHAR2(5 BYTE)                NOT NULL,

  COMP_NAME   VARCHAR2(80 BYTE)               NOT NULL,

  HIGHER_COMP VARCHAR2(5 BYTE)

)

TABLESPACEUSERS

PCTUSED    0

PCTFREE    10

INITRANS   1

MAXTRANS   255

STORAGE    (

            INITIAL          64K

            MINEXTENTS       1

            MAXEXTENTS       2147483645

            PCTINCREASE      0

            BUFFER_POOL      DEFAULT

           )

LOGGING

NOCACHE

NOPARALLEL

NOMONITORING;

插入初始数据：

INSERTINTO COMP_INFO VALUES('10000','XX公司','');

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

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

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

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

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

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

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

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

INSERTINTO 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、   查询整整个树的层次结构

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

a.comp_name单位名称 fromcomp_info a

startwith higher_comp is null

connectby 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、查询北京研究中心及下属单位：（顶——底）

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

a.comp_name单位名称 fromcomp_info a

startwith comp_id='12100'

connectby PRIOR comp_id=  higher_comp

结果：

上级公司

公司名称

XX公司中国公司

XX公司中国公司北京研究中心

        XX公司中国公司北京研究中心

XX公司中国公司北京研究一所

        XX公司中国公司北京研究中心

XX公司中国公司北京研究二所

        XX公司中国公司北京研究中心

XX公司中国公司北京研究三所

        XX公司中国公司北京研究中心

XX公司中国公司北京研究四所

 

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

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

a.comp_name单位名称 fromcomp_info a

startwith comp_id='12100'

connectby higher_comp= PRIOR  comp_id

结果：

上级公司

公司名称

XX公司中国公司

XX公司中国公司北京研究中心

        XX公司

XX公司中国公司

               

XX公司

 

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

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

a.comp_name单位名称 fromcomp_info a

startwith higher_comp='12100'

connectby PRIOR  comp_id= higher_comp

 

结果：

上级公司

公司名称

XX公司中国公司北京研究中心

XX公司中国公司北京研究一所

XX公司中国公司北京研究中心

XX公司中国公司北京研究二所

XX公司中国公司北京研究中心

XX公司中国公司北京研究三所

XX公司中国公司北京研究中心

XX公司中国公司北京研究四所

 

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

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

a.comp_name单位名称 fromcomp_info a

startwith higher_comp='12100'

connectby 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公司