这段时间做个Oracle的层级查询的问题,涉及到start with connect by的使用。先说一下表的结构和具体要得到什么样的结果集。
userid gender age managerid
kt001 m 42 nomanager
kt008 f 37 nomanager
kt002 m 43 kt001
kt003 f 38 kt001
kt004 m 41 kt002
kt005 m 39 kt002
kt006 f 38 kt003
kt007 f 39 kt003
kt009 f 36 kt008
kt010 f 39 kt009
kt011 f 38 kt009
managerid相同的user组成一个manager team,像kt002,kt003是一个team.
条件是:1.如果user所在的team存在gender = 'm' 或者age >= 40就把这个user display出来。
2.如果在当前的team中不存在这样的user,就在manager 的managerteam中查找,一直找到最后。
3.在向上查找的过程中,如果可以找到这样的team,就把路径上的所有user都得到。找不到就不显示。
像kt002和kt003是一个team的, 它们的managerid是kt001。在kt001这个team中因为kt002满足条件,所以kt002和kt003都是满足条件的结果。
又在kt003的team中,因为kt006和kt007都不满足条件,所以要到kt003的manager team中查找,也就是kt001的team.因为kt001 team满足条件,所以kt006 kt007也都满足条件。
在kt009的team中,kt010和kt011都不满足条件。在kt009的manager team中(kt008)也不满足条件,一直到最后都没满足条件的,所以kt010和kt011都不满足条件。
最后得到的结果集应该是:
userid gender age managerid tree group #
kt001 m 42 nomanager /nomanager group#3
kt002 m 43 kt001 /kt001 group#2
kt003 f 38 kt001 /kt001 group#2
kt004 m 41 kt002 /kt001/kt002 group#1
kt005 m 39 kt002 /kt001/kt002 group#1
kt006 f 38 kt003 /kt001/kt002/kt003 group#1
kt007 f 39 kt003 /kt001/kt002/kt003 group#1
因为在group列中,kt003 team不满足条件,在kt002中满足条件,所以它们是一个group的。
我得做法是首先得到所以满足条件的team,然后用层级搜索得到一个idtree,在判断满足条件的team 的managerid
是不是在这string上,如果这样像kt003team就被排除掉了, group也没有办法得到。
select ss.managerid, ss.userid, LPAD('', 2*level-1)||SYS_CONNECT_BY_PATH(ss.managerid, '/') idtree from
(
select
manager.userid as managerid , us.userid as userid from
ttable us
, ttable manager
where us.managerid = manager.userid
and us.gender = 'm'
and us.age >= 40
) ss
, (
select
u1.userid ,u1.manager
, level LEVEL_IN_ORG
, LPAD('', 2*level-1)||SYS_CONNECT_BY_PATH(u1.userid, '/') idtree
, u1.manager managerid
from change_on_setup.ttable u1
start with u1.manager = 'NO_MANAGER'
connect by prior u1.userid = u1.manager
)le
where instr(le.idtree,ss.managerid) > 0
这段代码只是表达了我的一个想法。
哪位牛人能给个好的方法。 在这谢谢了 。(不可以用function,只可以在select里表示)
2009年8月15日 11:05