java 迭代查询_Oracle 迭代查询

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

ref:http://blog.csdn.net/kenny1985/article/details/3980583

posted on 2012-11-28 14:13 游雯 阅读(6211) 评论(0)  编辑  收藏 所属分类: Java技术

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值