树形结构sql的进一步理解
来源:互联网 发布:多个表格数据汇总公式 编辑:程序博客网 时间:2024/06/07 07:12
树形结构的进一步理解
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
- 树形结构sql的进一步理解
- 树形结构的sql语句
- webservice的进一步理解
- LiveMesh的进一步理解
- 多态性的进一步理解
- 指针的进一步理解
- memset的进一步理解
- 宏的进一步理解
- NSRunLoop的进一步理解
- NSRunLoop的进一步理解
- NSRunLoop的进一步理解
- NSRunLoop的进一步理解
- NSRunLoop的进一步理解
- const的进一步理解~
- ajax的进一步理解
- HashMap的进一步理解
- 理解各种树形结构
- 有关树形结构的查询--Sql
- vue简单路由(一)
- <a> 标签的href onclick可以同时触发
- 《ES6入门》读书笔记之let和const命令
- Exception用了throws 就不要用catch
- Fragment详解之二——基本使用方法
- 树形结构sql的进一步理解
- palindrome-partitioning
- 【纸飞机(PaperPlane)IMITATE版】知识点整理
- java.lang.AbstractMethodError: org.slf4j.impl.Log4jLoggerAdapter.log
- map转化为list
- 树形结构list构建(树形实体)
- Kettle7.0抽取GIS数据到翰云库
- response.getWriter().write()凡是能直接接jsp,html的都可以用
- 左侧菜单的设计