树形查询一般用于上下级场合，使用的特殊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