ORACLE-FUNCTION-SYS_CONNECT_BY_PATH

最新推荐文章于 2022-11-01 14:41:09 发布

iteyer

最新推荐文章于 2022-11-01 14:41:09 发布

阅读量422

点赞数

CC 4.0 BY-SA版权

分类专栏： oracle-function

本文链接：https://blog.youkuaiyun.com/iteyer/article/details/49231725

oracle-function 专栏收录该内容

2 篇文章

订阅专栏

本文介绍了一个使用 Oracle SQL 的 CONNECT BY 功能来构建层级结构的示例。通过详细的表结构、查询语句及预期结果，展示了如何追踪每个节点的父子关系，并确定节点在层级中的位置。

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

表结构：

create table test_connect_by (child number,parent number);

原表数据：

CHILD   PARENT
2   5
3   5
10   15
5   15
9   17
8   17
15   38
17   38
6   38
13   26
1   26
12   26
11   18
7   18
38
26
18
期望结果：

当前节点   当前节点父节点   枝干关系   当前节点所在层   根节点   当前叶子节点   是否叶子节点
38       ->38   1   38       否
17   38   ->38->17   2   38       否
9   17   ->38->17->9   3   38   9   是
8   17   ->38->17->8   3   38   8   是
15   38   ->38->15   2   38       否
10   15   ->38->15->10   3   38   10   是
5   15   ->38->15->5   3   38       否
3   5   ->38->15->5->3   4   38   3   是
2   5   ->38->15->5->2   4   38   2   是
6   38   ->38->6   2   38   6   是
26       ->26   1   26       否
13   26   ->26->13   2   26   13   是
12   26   ->26->12   2   26   12   是
1   26   ->26->1   2   26   1   是
18       ->18   1   18       否
11   18   ->18->11   2   18   11   是
7   18   ->18->7   2   18   7   是

查询语句：

SELECT A.CHILD 当前节点,
       --A.PARENT 当前节点父节点,
       PRIOR A.CHILD 当前节点父节点,
       SYS_CONNECT_BY_PATH(CHILD, '->') 枝干关系,
       LEVEL 当前节点所在层,
       CONNECT_BY_ROOT A.CHILD 根节点,
       DECODE(CONNECT_BY_ISLEAF, 1, A.CHILD, NULL) 当前叶子节点,
       DECODE(CONNECT_BY_ISLEAF, 1, '是', '否') 是否叶子节点
FROM TEST_CONNECT_BY A
--WHERE (a.child = '15' OR a.parent = '15')
START WITH A.PARENT IS NULL --从parent为空开始扫描
CONNECT BY PRIOR A.CHILD = A.PARENT --以child为父列连接parent
-- ORDER BY CHILD;
ORDER SIBLINGS BY CHILD DESC --对层次排序
测试数据插入语句：

INSERT INTO TEST_CONNECT_BY (CHILD, PARENT) VALUES (2, 5);
INSERT INTO TEST_CONNECT_BY (CHILD, PARENT) VALUES (3, 5);
INSERT INTO TEST_CONNECT_BY (CHILD, PARENT) VALUES (10, 15);
INSERT INTO TEST_CONNECT_BY (CHILD, PARENT) VALUES (5, 15);
INSERT INTO TEST_CONNECT_BY (CHILD, PARENT) VALUES (9, 17);
INSERT INTO TEST_CONNECT_BY (CHILD, PARENT) VALUES (8, 17);
INSERT INTO TEST_CONNECT_BY (CHILD, PARENT) VALUES (15, 38);
INSERT INTO TEST_CONNECT_BY (CHILD, PARENT) VALUES (17, 38);
INSERT INTO TEST_CONNECT_BY (CHILD, PARENT) VALUES (6, 38);
INSERT INTO TEST_CONNECT_BY (CHILD, PARENT) VALUES (13, 26);
INSERT INTO TEST_CONNECT_BY (CHILD, PARENT) VALUES (1, 26);
INSERT INTO TEST_CONNECT_BY (CHILD, PARENT) VALUES (12, 26);
INSERT INTO TEST_CONNECT_BY (CHILD, PARENT) VALUES (11, 18);
INSERT INTO TEST_CONNECT_BY (CHILD, PARENT) VALUES (7, 18);
INSERT INTO TEST_CONNECT_BY (CHILD, PARENT) VALUES (38, NULL);
INSERT INTO TEST_CONNECT_BY (CHILD, PARENT) VALUES (26, NULL);
INSERT INTO TEST_CONNECT_BY (CHILD, PARENT) VALUES (18, NULL);
COMMIT;