Oracle Start With Connect By

本文介绍如何使用StartWithConnectBy在Oracle数据库中实现树形结构查询。通过创建包含父子关系的数据表,并插入多层级数据,演示了如何利用StartWithConnectBy获取指定节点及其子节点的所有记录。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

Start With Connect By 是用来实现在一个表中存在父子关系的两个字段的树形结构,通过一个从一个字段开始,以树形结构去查找其子记录。

1、建表

create table TEST_STRAT_WTH111
(
OID VARCHAR2(20),
PARENT_ID VARCHAR2(20)
)

2、插入数据

insert into test_strat_wth (OID, PARENT_ID)
values ('0000005', '00');

insert into test_strat_wth (OID, PARENT_ID)
values ('0000003', '00');

insert into test_strat_wth (OID, PARENT_ID)
values ('0000004', '00');

insert into test_strat_wth (OID, PARENT_ID)
values ('000000500001', '0000005');

insert into test_strat_wth (OID, PARENT_ID)
values ('0000006', '00');

insert into test_strat_wth (OID, PARENT_ID)
values ('000000600001', '0000006');

insert into test_strat_wth (OID, PARENT_ID)
values ('000000600002', '0000006');

insert into test_strat_wth (OID, PARENT_ID)
values ('0000007', '00');

insert into test_strat_wth (OID, PARENT_ID)
values ('0000008', '00');

insert into test_strat_wth (OID, PARENT_ID)
values ('0000009', '00');

insert into test_strat_wth (OID, PARENT_ID)
values ('000000900001', '0000009');

insert into test_strat_wth (OID, PARENT_ID)
values ('000000900002', '0000009');

insert into test_strat_wth (OID, PARENT_ID)
values ('000000900003', '0000009');

insert into test_strat_wth (OID, PARENT_ID)
values ('000000900004', '0000009');

insert into test_strat_wth (OID, PARENT_ID)
values ('00', '');

insert into test_strat_wth (OID, PARENT_ID)
values ('01', '');

insert into test_strat_wth (OID, PARENT_ID)
values ('000000500002', '0000005');

insert into test_strat_wth (OID, PARENT_ID)
values ('000000500003', '0000005');

insert into test_strat_wth (OID, PARENT_ID)
values ('000000500004', '0000005');

insert into test_strat_wth (OID, PARENT_ID)
values ('0000011', '00');

insert into test_strat_wth (OID, PARENT_ID)
values ('0100004', '01');

insert into test_strat_wth (OID, PARENT_ID)
values ('0100005', '01');

insert into test_strat_wth (OID, PARENT_ID)
values ('010000500001', '0100005');

insert into test_strat_wth (OID, PARENT_ID)
values ('010000500002', '0100005');

insert into test_strat_wth (OID, PARENT_ID)
values ('0100006', '01');

insert into test_strat_wth (OID, PARENT_ID)
values ('010000600001', '0100006');

insert into test_strat_wth (OID, PARENT_ID)
values ('010000600002', '0100006');

insert into test_strat_wth (OID, PARENT_ID)
values ('0000010', '00');

insert into test_strat_wth (OID, PARENT_ID)
values ('000001000001', '0000010');

insert into test_strat_wth (OID, PARENT_ID)
values ('000001000005', '0000010');

insert into test_strat_wth (OID, PARENT_ID)
values ('000001000003', '0000010');

insert into test_strat_wth (OID, PARENT_ID)
values ('000001000004', '0000010');

insert into test_strat_wth (OID, PARENT_ID)
values ('0100007', '01');

insert into test_strat_wth (OID, PARENT_ID)
values ('0100008', '01');

insert into test_strat_wth (OID, PARENT_ID)
values ('0100010', '01');

insert into test_strat_wth (OID, PARENT_ID)
values ('0100009', '01');

COMMIT;

3、查询数据

SELECT *
FROM TEST_STRAT_WTH T
START WITH T.PARENT_ID IS NULL
CONNECT BY PRIOR T.OID = T.PARENT_ID

4、数据结果:

00
0000005 00
000000500001 0000005
000000500002 0000005
000000500003 0000005
000000500004 0000005
0000003 00
0000004 00
0000006 00
000000600001 0000006
000000600002 0000006
0000007 00
0000008 00
0000009 00
000000900001 0000009
000000900002 0000009
000000900003 0000009
000000900004 0000009
0000011 00
0000010 00
000001000001 0000010
000001000005 0000010
000001000003 0000010
000001000004 0000010
01
0100004 01
0100005 01
010000500001 0100005
010000500002 0100005
0100006 01
010000600001 0100006
010000600002 0100006
0100007 01
0100008 01
0100010 01
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值