Oracle Start With Connect By-优快云博客

本文链接：https://blog.youkuaiyun.com/iteye_10599/article/details/81679688

本文介绍如何使用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