树形查询一般用于上下级场合，使用的特殊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('','Gates');
insert into tb_hierarchy(id,pid,name) values('','','Alice');
insert into tb_hierarchy(id,pid,name) values('','','Bill');
insert into tb_hierarchy(id,pid,name) values('','','Cindy');
insert into tb_hierarchy(id,pid,name) values('','','Douglas');
insert into tb_hierarchy(id,pid,name) values('','','Eliot');
insert into tb_hierarchy(id,pid,name) values('','','Mick');
insert into tb_hierarchy(id,pid,name) values('','','Flex');
insert into tb_hierarchy(id,pid,name) values('','','张三');
insert into tb_hierarchy(id,pid,name) values('','','李四');
insert into tb_hierarchy(id,pid,name) values('','','王五');
先让我们查出员工及其上级：
--列出员工和上级
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('','Gates');
insert into tb_hierarchy(id,pid,name) values('','','Alice');
insert into tb_hierarchy(id,pid,name) values('','','Bill');
insert into tb_hierarchy(id,pid,name) values('','','Cindy');
insert into tb_hierarchy(id,pid,name) values('','','Douglas');
insert into tb_hierarchy(id,pid,name) values('','','Eliot');
insert into tb_hierarchy(id,pid,name) values('','','Mick');
insert into tb_hierarchy(id,pid,name) values('','','Flex');
insert into tb_hierarchy(id,pid,name) values('','','张三');
insert into tb_hierarchy(id,pid,name) values('','','李四');
insert into tb_hierarchy(id,pid,name) values('','','王五'); --列出员工和上级
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