Oracle中树形查询使用方法

树形查询一般用于上下级场合，使用的特殊sql语法包括level,prior,start with,connect by等，下面将就实例来说明其用法。

表定义：

create table tb_hierarchy(
     id number(4,0) primary key,
     name nvarchar2(20) not null,
     pid number(4,0))

充值：

insert into tb_hierarchy(id,name) values('1','Gates');
insert into tb_hierarchy(id,pid,name) values('2','1','Alice');
insert into tb_hierarchy(id,pid,name) values('3','2','Bill');
insert into tb_hierarchy(id,pid,name) values('4','2','Cindy');
insert into tb_hierarchy(id,pid,name) values('5','2','Douglas');
insert into tb_hierarchy(id,pid,name) values('6','1','Eliot');
insert into tb_hierarchy(id,pid,name) values('7','6','Mick');
insert into tb_hierarchy(id,pid,name) values('8','6','Flex');
insert into tb_hierarchy(id,pid,name) values('9','7','张三');
insert into tb_hierarchy(id,pid,name) values('10','7','李四');
insert into tb_hierarchy(id,pid,name) values('11','7','王五');

先让我们查出员工及其上级：

--列出员工和上级
select level,id,name,(prior name) as mngName
from tb_hierarchy
start with pid is NULL
connect by (prior id)=pid

查询结果：

SQL> select level,id,name,(prior name) as mngName
  2  from tb_hierarchy
  3  start with pid is NULL
  4  connect by (prior id)=pid;

     LEVEL         ID NAME                                     MNGNAME
---------- ---------- ---------------------------------------- ----------------------------------------
         1          1 Gates
         2          2 Alice                                    Gates
         3          3 Bill                                     Alice
         3          4 Cindy                                    Alice
         3          5 Douglas                                  Alice
         2          6 Eliot                                    Gates
         3          7 Mick                                     Eliot
         4          9 张三                                     Mick
         4         10 李四                                     Mick
         4         11 王五                                     Mick
         3          8 Flex                                     Eliot

已选择11行。

从上面的level一列可以看出，Gates居于公司领导核心，属于董事长；他下面是alice，处于总经理地位；Alice下面有三个经理，分别是Bill，Cindy，Douglas...

这些结果是怎么查出来的呢？让我们看看SQL：

select level,id,name,(prior name) as mngName
from tb_hierarchy
start with pid is NULL
connect by (prior id)=pid

解读：

level：属于关键字，是和rownum一样的伪列，代表节点在整棵树中的层级，如Flex处于等级三，他上面有Eliot，Eliot上面有总头头Gates。

prior name:prior属于关键字，代表本条记录的上一条，如本条是（3，8，Flex）；那么prior就是（2，6，Eliot）；知道了prior是哪一条记录，我们就知道了prior name是Eliot，prior id就是6。

start with：这个语法告诉树形查询应该以pid是空的记录作为树的起点。

下面我们来查查以Mick为起点会是什么效果：

SQL> select level,id,name,(prior name) as mngName
  2  from tb_hierarchy
  3  start with name='Mick'
  4  connect by (prior id)=pid;

     LEVEL         ID NAME                                     MNGNAME
---------- ---------- ---------------------------------------- ----------------------------------------
         1          7 Mick
         2          9 张三                                     Mick
         2         10 李四                                     Mick
         2         11 王五                                     Mick

结果查询出了以Mick为组长,张三李四王五为组员的苦逼外包小组。

在国企干活的人一般称底下做事的为员，管员的人为基层干部，上下都是干部的为中层干部，上面再没人的则是首长。

下面我们查查谁是员，谁是基层领导干部，谁是中层领导干部，谁是首长：

SQL> select level,id,name,(prior name) as mngName,
  2  decode(level,1,1) as 首长,
  3  decode(level,2,1) as 中层干部,
  4  decode(level,3,1) as 基层干部,
  5  decode(connect_by_isleaf,1,1) as 员工
  6  from tb_hierarchy
  7  start with pid is NULL
  8  connect by (prior id)=pid;

     LEVEL         ID NAME                 MNGNAME                    首长   中层干部   基层干部       员工
---------- ---------- -------------------- -------------------- ---------- ---------- ---------- ----------
         1          1 Gates                                              1
         2          2 Alice                Gates                                    1
         3          3 Bill                 Alice                                               1          1
         3          4 Cindy                Alice                                               1          1
         3          5 Douglas              Alice                                               1          1
         2          6 Eliot                Gates                                    1
         3          7 Mick                 Eliot                                               1
         4          9 张三                 Mick                                                           1
         4         10 李四                 Mick                                                           1
         4         11 王五                 Mick                                                           1
         3          8 Flex                 Eliot                                               1          1

已选择11行。

上面的语法中多了一个关键字connect_by_isleaf，它表示当前节点下面没有子节点，或是当前记录下没有地位更低的记录（996！最苦逼的一群人）

下面SQL可以把id前面加点层次：

SQL> select lpad(' ',level,' ')||id AS padid,
  2  level,id,name,(prior name) as mngName,
  3  decode(level,1,1) as 首长,
  4  decode(level,2,1) as 中层干部,
  5  decode(level,3,1) as 基层干部,
  6  decode(connect_by_isleaf,1,1) as 员工
  7  from tb_hierarchy
  8  start with pid is NULL
  9  connect by (prior id)=pid;

PADID           LEVEL         ID NAME       MNGNAME                    首长   中层干部   基层干部    员工
---------- ---------- ---------- ---------- -------------------- ---------- ---------- ---------- ----------
 1                  1          1 Gates                                    1
  2                 2          2 Alice      Gates                                    1
   3                3          3 Bill       Alice                                               1       1
   4                3          4 Cindy      Alice                                               1       1
   5                3          5 Douglas    Alice                                               1       1
  6                 2          6 Eliot      Gates                                    1
   7                3          7 Mick       Eliot                                               1
    9               4          9 张三       Mick                                                        1
    10              4         10 李四       Mick                                                        1
    11              4         11 王五       Mick                                                        1
   8                3          8 Flex       Eliot                                               1       1

已选择11行。

下面把每个人的上级全列出来：

SQL> col path format a30;
SQL> select level,id,name,(prior name) as mngName,
  2  sys_connect_by_path(name,',') as path
  3  from tb_hierarchy
  4  start with pid is NULL
  5  connect by (prior id)=pid;

     LEVEL         ID NAME       MNGNAME              PATH
---------- ---------- ---------- -------------------- ------------------------------
         1          1 Gates                           ,Gates
         2          2 Alice      Gates                ,Gates,Alice
         3          3 Bill       Alice                ,Gates,Alice,Bill
         3          4 Cindy      Alice                ,Gates,Alice,Cindy
         3          5 Douglas    Alice                ,Gates,Alice,Douglas
         2          6 Eliot      Gates                ,Gates,Eliot
         3          7 Mick       Eliot                ,Gates,Eliot,Mick
         4          9 张三       Mick                 ,Gates,Eliot,Mick,张三
         4         10 李四       Mick                 ,Gates,Eliot,Mick,李四
         4         11 王五       Mick                 ,Gates,Eliot,Mick,王五
         3          8 Flex       Eliot                ,Gates,Eliot,Flex

已选择11行。

--2020年4月18日--

以上用到的全部SQL：

create table tb_hierarchy(
     id number(4,0) primary key,
     name nvarchar2(20) not null,
     pid number(4,0))
     
insert into tb_hierarchy(id,name) values('1','Gates');
insert into tb_hierarchy(id,pid,name) values('2','1','Alice');
insert into tb_hierarchy(id,pid,name) values('3','2','Bill');
insert into tb_hierarchy(id,pid,name) values('4','2','Cindy');
insert into tb_hierarchy(id,pid,name) values('5','2','Douglas');
insert into tb_hierarchy(id,pid,name) values('6','1','Eliot');
insert into tb_hierarchy(id,pid,name) values('7','6','Mick');
insert into tb_hierarchy(id,pid,name) values('8','6','Flex');
insert into tb_hierarchy(id,pid,name) values('9','7','张三');
insert into tb_hierarchy(id,pid,name) values('10','7','李四');
insert into tb_hierarchy(id,pid,name) values('11','7','王五');

--列出员工和上级
select level,id,name,(prior name) as mngName
from tb_hierarchy
start with pid is NULL
connect by (prior id)=pid

--以mick为起点
select level,id,name,(prior name) as mngName
from tb_hierarchy
start with name='Mick'
connect by (prior id)=pid

--列出是员，基层干部，中级干部和首长
select level,id,name,(prior name) as mngName,
decode(level,1,1) as 首长,
decode(level,2,1) as 中层干部,
decode(level,3,1) as 基层干部,
decode(connect_by_isleaf,1,1) as 员工
from tb_hierarchy
start with pid is NULL
connect by (prior id)=pid

--加入层次列
select lpad(' ',level,' ')||id AS padid,
level,id,name,(prior name) as mngName,
decode(level,1,1) as 首长,
decode(level,2,1) as 中层干部,
decode(level,3,1) as 基层干部,
decode(connect_by_isleaf,1,1) as 员工
from tb_hierarchy
start with pid is NULL
connect by (prior id)=pid

--把上级在path里全列出来
select level,id,name,(prior name) as mngName,
sys_connect_by_path(name,',') as path
from tb_hierarchy
start with pid is NULL
connect by (prior id)=pid