在Oracle里有一个不错的阶层式查询功能,可以用很简短的Script来达成目标,不用一堆子查询或是temp Table在那join来join去啰.

　　在Oracle里有一个不错的阶层式查询功能,可以用很简短的Script来达成目标,阶层的意思就有点像是(总管理->副总->协理->管理->副理…..)的这种层级关系,这样看可能还是很难懂阶层或查询的用法关系,以下是Demo的数据表:

 
TableName : UserMgrInfo

UserNo [人员编号]	UserName [人员名称]	UserMgrNo [主管代号]	UserActive [人员生失效]
Z001	Jeff	X059	1
X059	Kevin	X043	1
X040	Judy	B001	1
X043	David	X040	0
B001	Andy	A001	1
A001	Frank		1
C001	Cindy	B001	1

 
　　上面这个表格的关系很简单,每笔数据都有一个主管代号,这个主管代号就是他的上一阶主管,当UserMgrNo为空时,代表为最上阶主管,阶层结束,之中也有人员生失效的控制,现在要做的是如何下一个查询,找出该员的主管清单?而且只要找出生效的人员。
 
　　在Oracle里有个Start with connect by prior可以使用,以下的语法就是找出该员的生效主管清单:
 
Select UserNo,UserName from
(
        Select UserNo,UserName,UserActive from UserMgrInfo
        Start with UserNo=’Z001’
        Connect by UserNo=prior UserMgrNo
)
Where UserActive=’1’
 
这个语法所下的条件,就是找出编号Z001这个人的主管清单,这个语法所输出的结果如下 :

UserNo [人员编号]	UserName [人员名称]
Z001	Jeff
X059	Kevin
X040	Judy
B001	Andy
A001	Frank

　　而编号X043的David并不会输出,因为他是失效人员,为什么要用子查询的方式去下UserActive=’1’,而不直接下在里面就好? 因为如果直接下在里面,这个查询结果就只会到X059就停下来了,这个情况就很像rownum :
 
Select * from UserMgrInfo where rownum=1
这时会带回第一笔数据
Select * from UserMgrInfo where rownum=2
这时并不是带回二笔数据,而是一笔也没有,如果要带回第二笔,就要用子查询的方式
Select * from
(
Select rownum,* from UserMgrInfo
)
Where rownum=2
 
　　回正题,如果此时要查Cindy编号C001的主管清单,同样的语法所带回的结果如下 :

UserNo [人员编号]	UserName [人员名称]
C001	Cindy
B001	Andy
A001	Frank

所以用这个Start wit connect by prior就可以很快的达成阶层式查询啰~ 
 
参考数据 :
START WITH and CONNECT BY in Oracle SQL
 

原文:大专栏  Start with connect by prior 阶层式查询用法