表结构:
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;
另可参考:http://hb.qq.com/a/20100412/003378.htm 有较详细说明