树形结构sql的进一步理解

树形结构的进一步理解

connect by...start with...的执行原理可以用以下一段程序的执行以及对存储过程RECURSE()的调用来说明： 

 

/* 遍历表中的每条记录，对比是否满足start with后的条件，如果不满足则继续下一条， 

如果满足则以该记录为根节点，然后调用RECURSE()递归寻找该节点下的子节点， 

如此循环直到遍历完整个表的所有记录 。*/ 

 

http://www.cnblogs.com/baiyixianzi/archive/2012/08/30/plsql12.html

 

是否满足start with中的，满足则以此为根节点通过connect by 中的规则组织成树

 select * from (select * from 

(   

     select distinct(t.id) id, t.name name,t.parentid  parentid ,0 leave1,'1' leave from opm_menufolder t left join  opm_menuitem oo on t.id=oo.menufolderid where oo.id in (select MenuItemId from OPM_ROLELIMIT where ROLEID='ff8080814dc41486014dcd88e7830030') or t.parentid is null

   union all select distinct(o.id) id, o.name name,o.menufolderid ,o.level1 leave1,'2' leave  from opm_menuitem o  where o.id in (select MenuItemId from OPM_ROLELIMIT where ROLEID='ff8080814dc41486014dcd88e7830030') 

   union all select distinct(l.id) id, l.name name,l.menuitemid ,l.level1 leave1,'3' leave  from opm_menuitemlimit l where l.id in (select MENUITEMLIMITID from OPM_ROLELIMIT where ROLEID='ff8080814dc41486014dcd88e7830030')

  

) 

start with parentid is null connect by prior id=parentid ORDER SIBLINGS BY parentid) 

 

 

SELECT DISTINCT D.*, C.URL_ADDRESS

  FROM TB_POWER_GROUP D LEFT JOIN (SELECT A.GROUP_KEY, B.URL_ADDRESS

                                     FROM TB_POWER_GROUPRELURL A,

                                          TB_POWER_URL         B

                                    WHERE A.URL_KEY = B.URL_KEY) C ON D.GROUP_KEY = C.GROUP_KEY

                                    WHERE FLAG =#{current_trade_kind}  START WITH D.GROUP_KEY IN

                                    (SELECT GROUP_KEY FROM TB_CUS_FIRMRELGROUP WHERE CUSTOMER_KEY = #{CUSTOMER_KEY})//以当前用户的组key作为根

                                     CONNECT BY PRIOR  PARENT_KEY = D.GROUP_KEY ORDER BY D.SORT_NUM //通过父子key相等组织树，左右没有关系

 

SELECT DISTINCT A.*, C.URL_ADDRESS

  FROM TB_POWER_GROUP A LEFT JOIN (SELECT A.GROUP_KEY, B.URL_ADDRESS

                                     FROM TB_POWER_GROUPRELURL A,

                                          TB_POWER_URL         B

                                    WHERE A.URL_KEY = B.URL_KEY) C ON A.GROUP_KEY = C.GROUP_KEY

 START WITH A.GROUP_KEY IN

            (SELECT DISTINCT T2.GROUP_KEY

               FROM TB_POWER_GROUP         T2,

                    TB_POWER_ROLESRELGROUP T4,

                    TB_POWER_USERRELROLES  T5

              WHERE T4.ROLES_KEY = T5.ROLES_KEY AND

                    T5.CUS_USER_KEY = :CUS_USER_KEY AND

                    T2.GROUP_KEY = T4.GROUP_KEY AND

                    T2.FLAG = #{current_trade_kind})

 

CONNECT BY A.PARENT_KEY = PRIOR A.GROUP_KEY and

           a.FLAG = #{current_trade_kind}

 ORDER BY A.SORT_NUM