你给的数据有问题SQL> select * from temp; LEV PART_NO COMPONENT_PART
--------- -------------------- --------------------
1 CC026-0203A SC026-0203-1A
1 CC026-0203A SC026-0203-2A
2 SC026-0203-1A MAALU-082
2 SC026-0203-2A SC026-0203-3A
3 SC026-0203-3A MAALU-083SQL> select * from (
2 select temp.* from temp start with PART_NO='CC026-0203A'
3 connect by prior component_part=part_no)
4 t where component_part not in(select part_no from temp start with PART_NO='CC026-0203A'
5 connect by prior component_part=part_no); LEV PART_NO COMPONENT_PART
--------- -------------------- --------------------
2 SC026-0203-1A MAALU-082
3 SC026-0203-3A MAALU-083SQL> select 'CC026-0203A' part_no,component_part from (
2 select temp.* from temp start with PART_NO='CC026-0203A'
3 connect by prior component_part=part_no)
4 t where component_part not in(select part_no from temp start with PART_NO='CC026-0203A'
5 connect by prior component_part=part_no);PART_NO COMPONENT_PART
----------- --------------------
CC026-0203A MAALU-082
CC026-0203A MAALU-083SQL>
--------- -------------------- --------------------
1 CC026-0203A SC026-0203-1A
1 CC026-0203A SC026-0203-2A
2 SC026-0203-1A MAALU-082
2 SC026-0203-2A SC026-0203-3A
3 SC026-0203-3A MAALU-083SQL> select * from (
2 select temp.* from temp start with PART_NO='CC026-0203A'
3 connect by prior component_part=part_no)
4 t where component_part not in(select part_no from temp start with PART_NO='CC026-0203A'
5 connect by prior component_part=part_no); LEV PART_NO COMPONENT_PART
--------- -------------------- --------------------
2 SC026-0203-1A MAALU-082
3 SC026-0203-3A MAALU-083SQL> select 'CC026-0203A' part_no,component_part from (
2 select temp.* from temp start with PART_NO='CC026-0203A'
3 connect by prior component_part=part_no)
4 t where component_part not in(select part_no from temp start with PART_NO='CC026-0203A'
5 connect by prior component_part=part_no);PART_NO COMPONENT_PART
----------- --------------------
CC026-0203A MAALU-082
CC026-0203A MAALU-083SQL>
A
/ \
B C
/ \
D E
\
F 我要找的就是树根A下面的所以叶子(D,F),这个算法不知怎样写.
若能够用function根据树根找到所以叶子也可以.
13:17:20 SQL> desc t;
名称 空? 类型
----------------------------------------- -------- ---------------------------- ID CHAR(1)
PARID CHAR(1)13:17:58 SQL> select * from t;
a
b a
c a
d b
e c
f e已选择6行。已用时间: 00: 00: 00.00
13:18:03 SQL> select id from t start with id='b' connect by prior id=parid
13:18:07 2 minus
13:18:07 3 select parid id from t start with id='b' connect by prior id=parid
;
d已用时间: 00: 00: 00.00
13:18:07 SQL> select id from t start with id='a' connect by prior id=parid
13:18:16 2 minus
13:18:16 3 select parid id from t start with id='a' connect by prior id=parid
;
d
f已用时间: 00: 00: 00.00