在oracle中从CONNECT-BY查询中删除重复的子树 [英] Removing duplicate subtrees from CONNECT-BY query in oracle
问题描述
我有一个格式为
CREATE TABLE tree_hierarchy (
id NUMBER (20)
,parent_id NUMBER (20)
);
INSERT INTO tree_hierarchy (id, parent_id) VALUES (2, 1);
INSERT INTO tree_hierarchy (id, parent_id) VALUES (4, 2);
INSERT INTO tree_hierarchy (id, parent_id) VALUES (9, 4);
当我运行查询时:-
SELECT id,parent_id,
CONNECT_BY_ISLEAF leaf,
LEVEL,
SYS_CONNECT_BY_PATH(id, '/') Path,
SYS_CONNECT_BY_PATH(parent_id, '/') Parent_Path
FROM tree_hierarchy
WHERE CONNECT_BY_ISLEAF<>0
CONNECT BY PRIOR id = PARENT_id
ORDER SIBLINGS BY ID;
我得到的结果是这样的:-
Result I am Getting is like this:-
"ID" "PARENT_ID" "LEAF" "LEVEL" "PATH" "PARENT_PATH"
9 4 1 3 "/2/4/9" "/1/2/4"
9 4 1 2 "/4/9" "/2/4"
9 4 1 1 "/9" "/4"
但是我需要一个只让我得到这个的Oracle Sql查询
But I need an Oracle Sql Query That gets me only this
"ID" "PARENT_ID" "LEAF" "LEVEL" "PATH" "PARENT_PATH"
9 4 1 3 "/2/4/9" "/1/2/4"
这是一个简单的示例,我以这种方式拥有1000多个记录.当我运行上述查询时,它会生成许多重复项.任何人都可以给我一个泛型查询,该查询将给出从叶到根的完整路径而没有重复.感谢您的提前帮助
This is a simpler example I have more that 1000 records in such fashion.When I run the above query,It is generating many duplicates.Can any one give me a generic query that will give complete path from leaf to root with out duplicates.Thanks for the help in advance
推荐答案
必须始终知道有限层次结构中的根节点. 根据定义: http://en.wikipedia.org/wiki/Tree_structure 根节点是没有父节点的节点. 要检查给定节点是否为根节点,请使用"parent_id"并在表中检查是否存在具有该ID的记录. 该查询可能看起来像这样:
The root node in finite hierarchy must be always known. According to the definition: http://en.wikipedia.org/wiki/Tree_structure the root node is a node that has no parents. To check if a given node is a root node, take "parent_id" and check in the table if exists a record with this id. The query might look like this:
SELECT id,parent_id,
CONNECT_BY_ISLEAF leaf,
LEVEL,
SYS_CONNECT_BY_PATH(id, '/') Path,
SYS_CONNECT_BY_PATH(parent_id, '/') Parent_Path
FROM tree_hierarchy th
WHERE CONNECT_BY_ISLEAF<>0
CONNECT BY PRIOR id = PARENT_id
START WITH not exists (
select 1 from tree_hierarchy th1
where th1.id = th.parent_id
)
ORDER SIBLINGS BY ID;
这篇关于在oracle中从CONNECT-BY查询中删除重复的子树的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!