Oracle 迭代查询 CONNECT BY PRIOR ……START WITH ……
来源:互联网 发布:fork软件 编辑:程序博客网 时间:2024/06/05 02:39
connect by prior start with 经常会被用到一个表中存在递归关系的时候。比如我们经常会将一个比较复杂的目录树存储到一个表中。或者将一些部门存储到一个表中,而这些部门互相有隶属关系。这个时候你就会用到connect by prior start with。
表结构:
create table TB_ORG
(
ORGID VARCHAR2(50) not null,
ORGNAME VARCHAR2(100),
ORGPARENT VARCHAR2(50),
)
SQL> select orgid,ORGPARENT from tb_org order by ORGid desc;
ORGID ORGPARENT
----------------- --------------------------------------------------
top_org
newsroom top_org
sjgt newsroom
bgjsyj newsroom
bgjs newsroom
test52 bgjsyj
test36 bgjsyj
test3 bgjs
test2 sjgt
test1 sjgt
tes4t1 bgjs
--子取父
select * from tb_org CONNECT BY PRIOR orgparent=orgid START WITH orgid='newsroom'
-----子取父变形 ()
select * from tb_org CONNECT BY PRIOR orgparent=orgid START WITH orgparent='newsroom'
--父取子(结果中没有父这条记录)
select * from tb_org CONNECT BY PRIOR orgid=orgparent START WITH orgparent='newsroom'
-----父取子变形(多了父这一级)
select * from tb_org connect by prior orgid=orgparent start with orgid ='newsroom'
注意orgid=orgparent 和orgparent=orgid
- Oracle 迭代查询 CONNECT BY PRIOR ……START WITH ……
- Oracle 迭代查询 CONNECT BY PRIOR ……START WITH ……
- Oracle中start with…connect by prior子句用法
- ORACLE start with… connect by prior 子句用法
- Oracle中start with…connect by prior子句用法
- Oracle中start with…connect by prior子句用法
- Oracle 树操作、递归查询(select…start with…connect by…prior)
- Oracle 树操作、递归查询(select…start with…connect by…prior)
- Oracle 树操作、递归查询(select…start with…connect by…prior)
- Oracle 树操作、递归查询(select…start with…connect by…prior)
- oracle 使用 START WITH…CONNECT BY PRIOR 实现递归查询
- Oracle 树操作(select…start with…connect by…prior)
- Oracle 树操作(select…start with…connect by…prior)
- Oracle 树操作(select…start with…connect by…prior)
- Oracle 树操作(select…start with…connect by…prior)
- Oracle 树操作(select…start with…connect by…prior)
- Oracle 树操作(select…start with…connect by…prior)
- Oracle 树操作(select…start with…connect by…prior)
- Sql Thinking
- linux笔记:crontab计划任务
- 关于access to the path is denied问题的解决
- 第 4章 线程管理:ACE 的同步和线程管理机制
- 【Python开发web】(2) -- 跟着教程步步来 -- QuickWiki
- Oracle 迭代查询 CONNECT BY PRIOR ……START WITH ……
- POJ 3177 Redundant Paths
- 5.2 ACE_Task(任务)
- 部分手机无法正常访问wap页面,或者访问了无光标索引
- C++ 注册表的读写
- 69种感冒药副作用致命 白加黑在列
- 致工商银行的挑战书
- 面向对象理论基础
- MSQL子查询的同时删除原表数据,查詢字段重複的記錄