日期:2014-05-17 浏览次数:20992 次
ID NAME P_ID YZBZ我想查树形,但要去除没有叶子节点的枝干(YZBZ为1的是为叶子节点)
1 fat_1 0 0
2 fat_2 0 0
3 fat_3 0 0
1_1 fold_1 1 0
1_2 fold_2 1 0
2_1 fold_3 2 0
2_2 fold_4 2 0
2_3 fold_5 2 0
3_1 fold_6 3 0
3_2 fold_7 3 0
3_3 fold_8 3 0
3_4 fold_9 3 0
1_1_1 root_1 1_1 1
1_1_2 root_2 1_1 1
1_2_1 root_3 1_2 1
2_1_1 root_4 2_1 1
2_1_2 root_5 2_1 1
2_3_1 root_6 2_3 1
3_1_1 root_7 3_1 1
3_2_1 root_8 3_2 1
3_3_1 root_9 3_3 1
select * from treetest t connect by prior id = P_id START WITH P_id = '0';
这里可以看到2_2、3_4没有能走到叶子节点,所以要去除,我想了个方法就是从叶子节点向上走,找出集合在走下来,这样能去除没有叶子的枝干,如下:
1 fat_1 0 0
1_1 fold_1 1 0
1_1_1 root_1 1_1 1
1_1_2 root_2 1_1 1
1_2 fold_2 1 0
1_2_1 root_3 1_2 1
2 fat_2 0 0
2_1 fold_3 2 0
2_1_1 root_4 2_1 1
2_1_2 root_5 2_1 1
2_2 fold_4 2 0
2_3 fold_5 2 0
2_3_1 root_6 2_3 1
3 fat_3 0 0
3_1 fold_6 3 0
3_1_1 root_7 3_1 1
3_2 fold_7 3 0
3_2_1 root_8 3_2 1
3_3 fold_8 3 0
3_3_1 root_9 3_3 1
3_4 fold_9 3 0
select * from (select distinct * from((select * from treetest connect by id = prior P_id START WITH YZBZ = '1'))) t connect by prior id = P_id START WITH P_id = '0';
1 1 fat_1 0 0但这样的查询效率太低了,数据少了还好,数据多了的话实在是太慢了,
2 1_1 fold_1 1 0
3 1_1_1 root_1 1_1 1
4 1_1_2 root_2 1_1 1
5 1_2 fold_2 1 0
6 1_2_1 root_3 1_2 1
7 2 fat_2 0 0
8 2_1 fold_3 2 0
9 2_1_1 root_4 2_1 1
10 2_1_2 root_5 2_1 1
11 2_3 fold_5 2 0
12 2_3_1 root_6 2_3 1
13 3 fat_3 0 0
14 3_1 fold_6 3 0
15 3_1_1 root_7 3_1 1
16 3_2 fold_7 3 0
17 3_2_1 root_8 3_2 1
18 3_3 fold_8 3 0
19 3_3_1 root_9 3_3 1
select * from treetest t
where CONNECT_BY_ISLEAF = yzbz
connect by prior id = P_id START WITH P_id = '0';